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