On March 7, 2016, Microsoft announced the availability of the RC0 Build of SQL Server 2016. One of the new features in the RC0 Build is Database Scoped Configurations, which gives you the ability to easily make several database-level configuration changes for things that were previously configured at the instance-level.

These include:

    • Setting MAXDOP for an individual database
    • Setting the query optimizer cardinality estimation model for an individual database (independent of the database compatibility level)
    • Enable or disable parameter sniffing for an individual database
    • Enable or disable query optimization hotfixes for an individual database (equivalent to TF 4199)
    • The ability to clear the plan cache for an individual database (without using a DBCC command)

If you are using AlwaysOn Availability Groups, the first four commands can also be used to affect the database-level configuration setting for ALL of the secondary database copies that are in a AG. You can set these values for just the Primary database, set the values in the secondary database(s) to the same value as the Primary database, or set the values in the secondary database(s) to different values from the Primary database.

There is also a new DMV, sys.database_scoped_configurations, which lets you see the configuration values for the current database, including the values for the Primary database and the secondary databases (if that database is part of an AlwaysOn AG). I have examples of these commands in the code in Figure 1.

   1: -- Database scoped T-SQL Commands in SQL Server 2016 RC0

   2: -- Glenn Berry

   3: -- SQLskills.com

   4:  

   5:  

   6:  

   7: -- Get database scoped configuration values for current database 

   8: SELECT configuration_id, name, [value] AS [value_for_primary], value_for_secondary

   9: FROM sys.database_scoped_configurations WITH (NOLOCK) OPTION (RECOMPILE);

  10:  

  11: -- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

  12: -- https://msdn.microsoft.com/en-us/library/mt629158.aspx

  13:  

  14: -- The commands for the secondary database affect ALL secondary databases in an AlwaysOn AG

  15:  

  16: USE [CHECKDBTest]

  17: GO

  18:  

  19: -- Enable legacy Cardinality Estimation for Primary database

  20: -- Equivalent to using TF 4136, except this is only for this database

  21: ALTER DATABASE SCOPED CONFIGURATION 

  22: SET LEGACY_CARDINALITY_ESTIMATION = ON;

  23: GO

  24:  

  25: -- Enable legacy Cardinality Estimation for Secondary database

  26: ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 

  27: SET LEGACY_CARDINALITY_ESTIMATION = ON;

  28: GO

  29:  

  30: -- Set legacy Cardinality Estimation for the Secondary database(s) to the same value as the Primary database

  31: -- This is the default setting

  32: ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 

  33: SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;

  34: GO

  35:  

  36:  

  37:  

  38: -- Disable parameter sniffing for Primary database

  39: ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

  40: GO

  41:  

  42: -- Disable parameter sniffing for Secondary database

  43: ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF;

  44: GO

  45:  

  46: -- Set parameter sniffing for the Secondary database(s) to the same value as the Primary database

  47: -- This is the default setting

  48: ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY    

  49: SET PARAMETER_SNIFFING = PRIMARY;

  50: GO

  51:  

  52:  

  53:  

  54: -- Enable query optimizer fixes for Primary database

  55: -- Equivalent to using TF 4199, except this is only for this database

  56: ALTER DATABASE SCOPED CONFIGURATION 

  57: SET QUERY_OPTIMIZER_HOTFIXES = ON;

  58: GO

  59:  

  60: -- Enable query optimizer fixes for Secondary database

  61: ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 

  62: SET QUERY_OPTIMIZER_HOTFIXES = ON;

  63: GO

  64:  

  65:  

  66: -- Set MAXDOP for Primary database

  67: ALTER DATABASE SCOPED CONFIGURATION 

  68: SET MAXDOP = 4;

  69: GO

  70:  

  71: -- Set MAXDOP for Secondary database(s)

  72: ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 

  73: SET MAXDOP = 4;

  74: GO

  75:  

  76: -- Set MAXDOP for the Secondary database(s) to the same value as the Primary database

  77: -- This is the default setting

  78: ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 

  79: SET MAXDOP = PRIMARY; 

  80:  

  81:  

  82: -- Clear plan cache for current database (only possible for Primary database)

  83: ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

  84: GO

Figure 1: Example Database Scoped Configuration Commands in SQL Server 2016