Trace flag 4199 is added to control multiple query optimizer changes previously made under multiple trace flags

Article translations Article translations
Article ID: 974006 - View products that this article applies to.
Expand all | Collapse all

On This Page

INTRODUCTION

Starting with Microsoft SQL Server 2000 Service Pack 3 (SP3), the SQL Server query processor team adopted a policy that any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag. Except for fixes to bugs that can cause incorrect results or corruption, these hotfixes are turned off by default, and a trace flag is required to enable the fix. This policy change helps avoid unexpected changes to the execution plan that may occur when a hotfix or a security update is installed.

Over time there were multiple fixes, each controlled with a different trace flag. This can make troubleshooting query performance difficult and time-consuming. To improve the troubleshooting process, trace flag 4199 was added in Cumulative Update 6 for SQL Server 2005 Service Pack 3 (SP3), Cumulative Update package 7 for SQL Server 2008, Cumulative Update 7 for SQL Server 2008 Service Pack 1 (SP1), and SQL Server 2008 R2. This one trace flag can be used to enable all the fixes that were previously made for the query processor under many trace flags. In addition, all future query processor fixes will be controlled by using this trace flag.

Note This policy change affects Microsoft SQL Server 2000 SP3 and later versions of SQL Server.

MORE INFORMATION

Because of the policy change to put query processor fixes under a trace flag, even if you are running with the latest hotfix or cumulative update installed, you are not necessarily running SQL Server with all the latest query processor fixes enabled. The fixes are only enabled by using a trace flag. Therefore, if you encounter a situation in which the query is not performing as expected, you must first determine whether you are experiencing a known issue or a new issue.

Over time, multiple fixes were released, and each was controlled by a different trace flag. Isolating a particular query that showed non-optimal behavior and determining whether the query was potentially hitting a known issue that was already addressed in a hotfix became difficult and time-consuming. Having a single trace flag to enable all the hotfixes enables customers to quickly test whether they are encountering an issue that might be addressed by a previous fix that can be enabled through the trace flag. All future query processor fixes will be controlled by trace flag 4199 and will not have a separate trace flag to enable the fix individually.

Previous fixes that can now be enabled by trace flag 4199 are as shown in this table:
Collapse this tableExpand this table
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*
* Note In SQL Server 2008 R2 release version, the trace flag 4135 was inadvertently omitted from the list of trace flags that can be controlled by -T4199. But 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. This feature was first released in Cumulative Update 6 for SQL Server 2005 SP3 and Cumulative Update package 7 for SQL Server 2008.

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.

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 how to obtain SQL Server 2005 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base:
913089 How to obtain the latest service pack for SQL Server 2005
For more information about the new features and improvements in SQL Server 2005 SP3, visit the following Microsoft website:
http://go.microsoft.com/fwlink/?LinkId=131442
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: June 27, 2012 - Revision: 7.0
APPLIES TO
  • 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
Keywords: 
kbsurveynew kbexpertiseadvanced kbqfe kbfix KB974006

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com