Problem
Microsoft recently launched SQL Server 2016 Release Candidate 2 which provides some exciting new features. In this tip we will look at the Database Scope Configuration. We are going to explore this new feature and demonstrate how to use it.
Solution
The following was done using SQL Server 2016 RC0, but SQL Server RC2 is now available as well.
Database Scoped Configurations
In SQL Server 2016 Management Studio, if we go to the Properties for a database and navigate to Options we can see a new grouping called “Database Scoped Configurations” which includes several key options.
SQL Server 2016 now supports database level configurations that affect the behavior of the application code at the database level. Previously we did not have these options at the database level, these options were applicable for the entire instance. With SQL Server 2016, if we have many databases on a single instance with a variety of configuration needs, now it is easy to have more control per database.
Database scoped configuration supports the features below:
- Legacy Cardinality Estimation: This options enables you to configure the query optimizer cardinality estimation model for an individual database which is independent of the database compatibility level.
- MAX DOP: Maximum degree of parallelism for an individual database, this can be values 0, 1, 2, etc.
- Parameter Sniffing: We can set the behavior for the query optimizer to use parameter sniffing or not. In previous versions of SQL Server we could disable this feature using trace flag 4136, but now we can control it at the database level.
- Query Optimizer Fixes: By using this feature we can enable or disable query optimization hotfixes for an individual database.
Something to note above is the For Secondary options, this is because these features are supported for AlwaysOn Availability Groups and different options can be set for the primary vs. the secondary databases.
DMV to Check Current Database Scoped Configurations
First, let’s see the default values for each of these options, we can check these by querying the sys.database_scoped_configurations DMV. We can see parameter sniffing is set to ON by default and all others are OFF.
If we set LEGACY_CARDINALITY_ESTIMATION to true and rerun the query we can see it is reflected using the DMV.
Clear Individual Database Plan Cache
Another option that is new at the database level is the abiltiy to clear the plan cache for an individual database. This is done using the T-SQL command below. Note: the option to clear the plan cache is only supported for the primary database if the database is in an Availability Groups.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
T-SQL to Set Database Scoped Configurations
Enable Legacy Cardinality Estimation
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
Disable Parameter Sniffing
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
Enable Query Optimizer Fixes
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
Set MAXDOP Value
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
Set Secondary Database Options
For AlwaysOn Availability Group secondary databases, the options can be set as follow. This is just one example and the other settings are done in a similar way.
-- Enable or disable option for secondary ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON; -- Set option for secondary to be the same as primary ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
Next Steps
It is good to explore new options to take more control over query execution for your databases and you even have control for secondary databases in AlwaysOn Availability Groups.
- Download and explore SQL Server 2016 Release Candidate 2
- Read more about Overview of 2016 RC0 Features
- Read more about Alter database scope configurations
- Read more about Parameter sniffing
- Check out all of the SQL Server 2016 tips
