- SQL Server 2005 Service Pack 3 (SP3) Cumulative Update 6
- SQL Server 2008 Service Pack 1 (SP1) Cumulative Update package 7
- SQL Server 2008 R2 (RTM)
In SQL Server 2016, trace flag 4199 hotfixes that are made to previous releases of SQL Server will become enabled under database COMPATIBILITY_LEVEL 130 without trace flag 4199 enabled. Trace flag 4199 will be used to release any future SQL Server 2016 hotfixes for databases by using the 130 compatibility level. Because trace flag 4199 is recommended only for customers who are seeing specific performance issues, customers are advised to remove trace flag 4199 after they migrate their databases to the latest compatibility level because trace flag 4199 will be reused for future fixes that may not apply to your application and could cause unexpected plan performance changes on a production system. This means that different trace flag 4199 hotfixes are enabled for each compatibility level that is supported in a given product release. The latest compatibility level already enables all previous fixes under trace flag 4199. This means that upgrading a database to the latest compatibility level and removing trace flag 4199 still enables all fixes that a workload was leveraging before database upgrade, just not new fixes. If at a later time customers experience query performance issues, experiment with enabling the database scoped option QUERY_OPTIMIZER_HOTFIXES or query hint ENABLE_QUERY_OPTIMIZER_HOTFIXES in a test environment to determine if the issues are resolved. Refer to the documentation about QUERY_OPTIMIZER_HOTFIXES.
Note By default, databases that are created in SQL Server 2016 use compatibility level 130 and have new optimizer logic already enabled.
The major advantage of this model is that it reduces risk for production systems during the upgrade process. This approach separates the installation of a new major version of SQL Server from the automatic enabling of all new query processor changes. Because major version upgrades change the file format and are not reversible, it is a good idea to use the COMPATIBILITY_LEVEL setting, because this enables a customer to quickly downgrade if an unexpected plan performance issue is found during an upgrade. If a customer finds an unexpected plan change that blocks an application upgrade, the customer can ease the situation by applying an appropriate plan hint by using the Query Store to force the prior plan, or can ease the situation by contacting Microsoft Customer Support to help with the issue to provide a workaround or hotfix. When all issues are lessened, the upgrade can continue. Customers should integrate this capability into their upgrade planning for SQL Server 2016.
The following table explains the model for how trace flag 4199 will work starting with SQL Server 2016.
|Setting||SQL compatibility level||Trace flag 4199||Optimizer hotfixes before SQL Server 2016 RTM||Optimizer hotfixes after SQL Server 2016 RTM|
|3.||130||Off||Enabled by compatibility level||Disabled|
|4.||130||On||Enabled by compatibility level||Enabled by compatibility level|
Note Setting no. 3 is recommended for customers who are newly upgrading to SQL Server 2016.
For major releases after SQL Server 2016, Microsoft plans to continue using this servicing model for optimizer hotfixes. By default, or each release, any trace flag 4199 hotfixes from the previous release will be enabled in the next compatibility level. This means that the recommended state for customers after they migrate to the latest compatibility level will be to have trace flag 4199 disabled. Later hotfixes would use trace flag 4199 to enable fixes for customers who have to enable those specific hotfixes in an application. Customers are advised to disable trace flag 4199 after an application is upgraded to the latest compatibility level to avoid having unexpected future optimizer changes enabled on an application unexpectedly. That upgrading to the latest compatibility level and removing trace flag 4199 still enables all fixes that a workload was leveraging before the upgrade, just not new fixes.
Note While many optimizer hotfixes are enabled under trace flag 4199, some use other trace flags. Trace flag 4199 historically covered trace flags that are widely applicable and likely to become enabled by default in a future release. Alternative trace flags are used in hotfixes where the conditions are very specific to only a few customers, where the fix may cause performance regressions in other kinds of applications, or where the internal functionality may see changes before it is ready to become enabled for everyone. Microsoft will continue to use other trace flags as needed to service the product.
Note This article focuses on the model for releasing trace flag 4199 hotfixes on the latest compatibility level of the latest product. (At publication time, this is SQL Server 2016.) Optimizer hotfixes may be released on older in-market versions of SQL Server or on lower compatibility levels (120 or prior) of SQL Server 2016. Microsoft will evaluate each case and determine whether to use trace flag 4199 or a different trace flag. Because non-hotfix changes are also enabled when a move is made to a later compatibility level, there is no specific promise that no plan changes will occur during an upgrade (with or without trace flag 4199). Customers should always test changes to the compatibility level carefully for production applications and should use mitigation technologies, such as the Query Store, if there is a plan-choice related performance issue.
For reference, the following table lists trace flags that were used for query processor hotfixes before the introduction of trace flag 4199.
|Microsoft Knowledge Base article||Trace flag|
SQL Server 2005
The fix for this issue was first released in Cumulative Update 6 for SQL Server 2005 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
SQL Server 2008The fix for this issue was first released in Cumulative Update 7. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:
SQL Server 2008 SP1
The fix for this issue was first released in Cumulative Update 7 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
SQL Server 2008 R2In the release version of SQL Server 2008 R2, the trace flag 4135 was inadvertently omitted from the list of trace flags that can be controlled by -T4199. However, this has been fixed in Cumulative Update 1 for SQL Server 2008 R2. So, for this build and for SQL Server 2005 and SQL Server 2008 supported editions, -T4199 will suffice to enable this and other trace flags that are listed in this article.
SQL Server 2012 and later versionsTrace flag 4199 is included in the release versions of SQL Server 2012 and later versions.
How to enable trace flag 4199You can enable trace flag 4199 at startup or in a user session. This trace flag has either global-level or session-level effect. To enable trace flag 4199, use the DBCC TRACEON command or use –T 4199 as a startup parameter.
If DBCC TRACEON\TRACEOFF is used this does not regenerate a new cached plan for stored procedures. Plans could be in cache that were created without the trace flag.
For detailed information about how to enable or disable trace flags and for explanations of global and session-level trace flags, refer to the following topics in SQL Server Books Online:
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
New naming schema for Microsoft SQL Server software update packages
Description of the standard terminology that is used to describe Microsoft software updates