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.
Using DMV sys.database_scoped_configurations , you can see all setting made under database scoped configurations.
