DATABASE SCOPED CONFIGURATION in SQL Server 2016

SQLServerF1

Microsoft SQL Server 2016 has many new features and enhancements to existing features. Also, Microsoft claims break through improvement in the performance of queries in SQL Server 2016, compared to its previous versions. Database Scoped Configurations is one of the interesting enhancement made in SQL Server 2016. In previous versions of SQL Server, we can open database properties and can view various details about the databases and also make changes to some of the database properties to alter the behavior of the database. Starting with Microsoft SQL Server 2016, in database properties window, options tab, new section has been added with the name “Database Scoped Configurations”, which includes different options which can be configured.

Legacy Cardinality Estimation – This options allows us to set the query optimizer cardinality estimation at an individual database level, which is different from the database compatibility level. In SQL Server 2016 there are a lot of enhancements that come when we set Compatibility Level 130 for a database in SQL Server 2016, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. But there can be situations where you want to benefit from the new compatibility, but still want the application to work well and run under old compatibility mode, then we can enable this option. Once we set the Compatibility Level of database to 130, then we will get all the performance benefits for query execution that come with SQL Server 2016, while still using old CE.
Legacy Cardinality Estimation For Secondary – This option can be set to PRIMARY or SECONDARY. This option can be set only the secondaries and indicates that the same configuration will be used which are set on the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries. While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.

MAXDOP – In previous versions of SQL Server, we can set MAXDOP at an instance level or at batch/stored procedure/query level, but not at the database level. But starting SQL Server 2016, we can set Maximum degree of parallelism(MAXDOP) for an individual database. SP_Configure MAXDOP setting will be overwritten by this database setting for queries ran against this database.
MAXDOP For Secondary – This option can be set to PRIMARY or SECONDARY. This option can be set only the secondaries and indicates that the same configuration will be used which are set on the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries. While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.

Parameter Sniffing – Using this option, we can enable or disable parameter sniffing at database level. In previous versions of SQL Server, we could turn on or off using the trace flag, but now it is made simple with just changing the option from database properties.
Parameter Sniffing For Secondary – This option can be set to PRIMARY or SECONDARY. This option can be set only the secondaries and indicates that the same configuration will be used which are set on the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries. While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.

Query Optimizer Fixes – In previous versions of SQL Server, there are many query optimizer hotfixes enabled by some trace flags. In SQL Server 2016, we can choose to either enable or disable query optimization hotfixes for an individual database.
Query Optimizer Fixes For Secondary – This option can be set to PRIMARY or SECONDARY. This option can be set only the secondaries and indicates that the same configuration will be used which are set on the primary. If the configuration for the primary changes, secondary will also adjust to the same value. PRIMARY is the default setting for the secondaries. While you can configure secondary databases to have different scoped configuration settings from their primary, all secondary databases will use the same configuration. Different settings cannot be configured for individual secondaries.
– Starting SQL Server 2016, we now have the ability to clear individual Database Plan Cache using command ALTER

DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Leave a Reply

Your email address will not be published. Required fields are marked *