SQL Server query optimizer hotfix trace flag 4199 servicing model

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
318530 4101
940128 4102
919905 4103
920346 4104
920347 4105
922438 4106
923849 4107
926024 4108
926773 4109
933724 4110
934065 4111
946793 4115
950880 4116
948445 4117
942659 4119
953948 4120
942444 4121
946020 4122
948248 4124
949854 4125
959013 4126
953569 4127
955694
957872
4128
958547 4129
956686 4131
958006 4133
960770 4135*

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
Свойства

Номер статьи: 974006 — последний просмотр: 10 июля 2015 г. — редакция: 1

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 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Standard

Отзывы и предложения