Database Scoped Configurations and Trace flag 4199

Those who are working on query performance whether you are a DBA or Developer I am sure you guys must have used trace flag 4199.  TF 4199 enables all query optimizer related fixes.Microsoft used to have separate TF for each hotfix applied to query optimizer prior to SQL Server 2005 Service Pack 3 (SP3) Cumulative Update 6. Starting from SQL Server 2005 Service Pack 3 (SP3) Cumulative Update 6, every hotfix related to query optimizer reflects using TF 4199.

From SQL Server 2016 onwards, the TF 4199 related hotfixes will be on under compatibility level 130. This trace flag will be used to release any future hotfixes using compatibility level. Microsoft recommends this trace flag only for those customers who are experiencing specific performance issue. If you are already migrated to SQL Server latest compatibility level the it is advisable to remove the use of trace flag 4199.because this trace flag will be used for future hotfixes. Suppose you are still using this trace flag and some hotfixes made to the optimizer which is impacting unexpected performance problem. So the hotfixes are based on compatibility level. If you upgrading to SQL Server 2016 and you needed required to enable this TF.

For more detail on TF 4199, I encourage everyone to read  Trace flag 4199 Service Model.

   

Database scoped configuration is a new feature introduced in SQL Server 2016. Using this now you can make many properties setting at database level which were only available to set at instance level prior to SQL Server 2016. Now using QUERY_OPTIMIZER_HOTFIXES you can enable query optimizer hotfixes at database level. This setting is equivalent to TF 4199.

USE [DemoDB];
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

Using SSMS also you can set this.

ScopedConfiguration

Using DMV sys.database_scoped_configurations , you can see all setting made under database scoped configurations.

   

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published.