Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Database Scoped Configuration Options


By:   |   Last Updated: 2016-04-13   |   Comments (2)   |   Related Tips: More > SQL Server 2016

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 Management Studio Database Scoped Configurations

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.

Querying the sys.database_scoped_configurations DMV

If we set LEGACY_CARDINALITY_ESTIMATION to true and rerun the query we can see it is reflected using the DMV.

Updated values from the sys.database_scoped_configurations 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.



Last Updated: 2016-04-13


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, October 09, 2016 - 4:08:44 PM - wilfred van Dijk Back To Top

Beware of this behaviour: Say your MaxDOP = 0, so all your database scoped values are also 0. If you change the global setting to another value, the database scoped values will not change and stick to 0

It would be handy of there's also a NULL value for database scoped configuration which means: always use the default instance setting (i'll make a request on connect)

 


Thursday, April 14, 2016 - 11:38:13 AM - Richard Willemain Back To Top

 Vey nicely presented.  Veryhelpful. Thanks

 


Learn more about SQL Server tools