New SQL Server 2016 SCOPED CONFIGURATION

Microsoft SQL Server 2016 introduced new way of configuring some of the instance level setting to database level which gives you the ability to easily make several database level configuration changes such as

  • Setting MAXDOP for an individual database
  • Set the query optimizer cardinality estimation model independent of the database to compatibility level.
  • Enable or disable parameter sniffing for an individual database
  • Enable or disable query optimization hotfixes for an individual database
  • Clear the plan cache for an individual database without using DBCC Command

Let’s us explore each option:

MAXDOP: 

Set the MAXDOP parameter to an arbitrary value (0,1,2, …) to control the maximum degree of parallelism for the queries in the database. It is recommended to switch to db-scoped configuration to set the MAXDOP instead of using sp_configure at the server level, especially for Azure SQL DB where sp_configure is not available. You can set the different MAXDOP settings for primary and secondary. For Example, you can set the MAXDOP value to 1 on a primary and on the secondary where used for reporting can be set to 4 as below.

-- Set MAXDOP for Primary database
 ALTER DATABASE SCOPED CONFIGURATION 
 SET MAXDOP = 1;
 GO

 -- Set MAXDOP for Secondary database(s)
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET MAXDOP = 4;
 GO

 

Legacy Cardinality Estimation:

Set the option “LEGACY_CARDINALITY_ESTIMATION” Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. This is equivalent to Trace Flag 9481. To set this at the instance level, see Trace Flags (Transact-SQL). To accomplish this at the query level, add the QUERYTRACEON query hint.

You can use the below sql statements to enable the legacy cardinality estimation for primary and secondary based on your requirement.

-- Enable legacy Cardinality Estimation for Primary database
 ALTER DATABASE SCOPED CONFIGURATION 
 SET LEGACY_CARDINALITY_ESTIMATION = ON;
 GO
 -- Enable legacy Cardinality Estimation for Secondary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET LEGACY_CARDINALITY_ESTIMATION = ON;
 GO

 -- Set legacy Cardinality Estimation for the Secondary database(s) 
--to the same value as the Primary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
 GO

 

Enable/Disable Parameter Sniffing:

Enables or disables parameter sniffing. This is equivalent to Trace Flag 4136. o set this at the instance level, see Trace Flags (Transact-SQL). To set this at the query level, see the OPTIMIZE FOR UNKNOWN query hint.

You can use the below sql statements to disable parameter siniffing.

 -- Disable parameter sniffing for Primary database
 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
 GO

 -- Disable parameter sniffing for Secondary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF;
 GO

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

 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET PARAMETER_SNIFFING = PRIMARY;
 GO

 

Query Optimizer Hotfixes:

Enable or disable “QUERY_OPTIMIZER_HOTFIXES” at the database level, to take advantage of the latest query optimizer hotfixes, regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199

Sample T-SQL to enable query optimizer hotfixes

-- Enable query optimizer fixes for Primary database
ALTER DATABASE SCOPED CONFIGURATION
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

-- Enable query optimizer fixes for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

 

Clear Procedure Cache:

“Clear Procedure Cache” option allows to clear procedure cache at the database level without impacting other databases.

 Sample T-SQL Script to clear the procedure cache
 -- Clear plan cache for current database (only possible for Primary database)
 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
That’s all about new sql server 2016 scoped configuration option.

Cheers

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

Advertisements

About Ramasankar

My name is Ramasankar Molleti, and I have been working on sql server over 10 years, primarily focusing on Database Administration, Architect , Development and Business intelligence of sql server. I also hold various Microsoft certifications (Microsoft Certified Information Technology Professional, Microsoft Certified Technology Specialist, and Microsoft Certified Solution Associate). Disclaimer The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights. The owner of https://sqlram.wordpress.com will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.
This entry was posted in Database Administration, SQL Server 2016. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s