INTRODUCTION
Versions of Microsoft SQL Server later than SQL Server 2000 Service Pack 3 (SP3) delivered most hotfixes to the query optimizer in an off-by-default state in order to prevent existing production customers from seeing expected plan changes that could cause performance regressions. At first, each hotfix was delivered under a separate trace flag. Later, this practice was changed so that most flags were combined under a single trace flag (4199). This new practice was initiated across multiple versions starting with the following updates:
-
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)
Trace flag 4199 was used to collect hotfixes that were intended to become on-by-default in a future release, whereas other trace flags were used for situations in which a fix was not intended to become on-by-default in current form. Starting with SQL Server 2016 RTM, the database COMPATIBILITY_LEVEL setting will be used enable trace flag 4199-related hotfixes on-by-default. This article describes the mechanics and policy of how plan-affecting hotfixes will be delivered for SQL Server 2016 and later versions.
More Information
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 |
|---|---|---|---|---|
|
1. |
120 |
Off |
Disabled |
Disabled |
|
2. |
120 |
On |
Enabled |
Disabled |
|
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 trace flags were used for query processor hotfixes before the introduction of trace flag 4199:
4101, 4102, 4103, 4104, 4105, 4106, 4107, 4108, 4109, 4110, 4111, 4115, 4116, 4117, 4119, 4120, 4121, 4122, 4124, 4125, 4126, 4127, 4128, 4129, 4131, 4133, 4135.
SQL Server 2012 and later versions
Trace flag 4199 is included in the release versions of SQL Server 2012 and later versions.
How to enable trace flag 4199
You 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:
References
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:
822499 New naming schema for Microsoft SQL Server software update packages For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates
