SQL Server query optimizer hotfix trace flag 4199 servicing model

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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.

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 levelTrace flag 4199 Optimizer hotfixes before SQL Server 2016 RTMOptimizer hotfixes after SQL Server 2016 RTM
1. 120OffDisabledDisabled
2. 120OnEnabledDisabled
3. 130OffEnabled by compatibility levelDisabled
4.130OnEnabled by compatibility levelEnabled 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.

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 articleTrace flag
3185304101
9401284102
9199054103
9203464104
9203474105
9224384106
9238494107
9260244108
9267734109
9337244110
9340654111
9467934115
9508804116
9484454117
9426594119
9539484120
9424444121
9460204122
9482484124
9498544125
9590134126
9535694127
955694
957872
4128
9585474129
9566864131
9580064133
9607704135*

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:
974648 Cumulative update package 6 for SQL Server 2005 Service Pack 3
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
960598 The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released
Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

SQL Server 2008

The 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:
973601 Cumulative update package 7 for SQL Server 2008
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released

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:
979065 Cumulative update package 7 for SQL Server 2008 Service Pack 1
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
970365 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released
Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

SQL Server 2008 R2

In 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 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
Properties

Article ID: 974006 - Last Review: 07/10/2015 01:43:00 - Revision: 10.0

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems, Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Workgroup, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Express, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Standard

  • kbsurveynew kbexpertiseadvanced kbqfe kbfix KB974006
Feedback