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

 

Published by Ramasankar

Hi. I’m Ramasankar Molleti. I’m a passionate IT professional with over 14 years of experience on providing solutions for customers who are looking on cloud computing, Database Migration, Development, and Big Data. I love learning new technologies and share my knowledge to community. I am currently working as Sr Cloud Architect with focus on Cloud Infrastructure, Big Data. I work with developers to architect, build, and manage cloud infrastructure, and services. I have deeep knowledge and experience on working with various database platforms such as MS SQL Server, PostgeSQL, Oracle, MongoDB, Redshift, Dyanamodb, Amazon Aurora. I worked as Database Engineer, Database Administrator, BI Developer and successfully transit myself into Cloud Architect with focus on Cloud infranstructure and Big Data. I live in USA and put my thoughts down on this blog. If you want to get in touch with me, contact me on my Linkedin here: https://www.linkedin.com/in/ramasankar-molleti-23b13218/ My Certifications: Amazon: AWS Certified Solutions Architect – Professional AWS Certified DevOps Engineer – Professional certificate AWS Certified Big Data – Specialty AWS Certified Security – Specialty certificate AWS Certified Advanced Networking – Specialty certificate AWS Certified Solutions Architect – Associate Microsoft: Microsoft® Certified Solutions Associate: SQL Server 2012/2014 Microsoft Certified Professional Microsoft® Certified IT Professional: Database Administrator 2008 Microsoft® Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: