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

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.

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:* 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: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: 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: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:

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: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: 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.

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: For more information about the new features and improvements in SQL Server 2005 SP3, visit the following Microsoft website: 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: For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: