Select the product you need help with

Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level

Article ID: 2801413 - View products that this article applies to.

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 helps avoid unexpected changes to the execution plans of existing workloads that may occur when a hotfix or a security update is installed.

Usually trace flags are enabled at startup or in a user session. However, this may have an unexpected effect on some queries in an existing database application. For example, consider an application or workload that includes multiple queries, and some of these queries use an inefficient query execution plan that is improved by enabling a trace flag that controls a corresponding hotfix. However, other queries may experience a less optimal execution plan when the same trace flag is applied. This is because the execution plan choice affects all queries that are compiled in the instance or the session when the corresponding trace flag is enabled. Depending on the query and the data, changing the models that are used by the query optimizer may both improve and decrease execution plan efficiency and compilation time for particular queries.

If a trace flag affects any query execution plan in an unwanted way, but improves some other query execution plan, you may want to enable a corresponding trace flag for only a particular query. You can do this by enabling the trace flag in a batch (by using DBCC TRACEON command) right before the target query, and then disabling the trace flag (by using DBCC TRACEOFF command) right after the query. However, this may not always be possible to control the Transact-SQL batch text for existing applications. You may experience poor query performance in an existing workload, and want to apply an available plan-affecting change to a query without changing the batch text itself. You can do this by using a query-level option to enable a trace flag for only a particular query.

Starting with Microsoft SQL Server 2005 Service Pack 2 (SP2) and Microsoft SQL Server 2008, the query-level option "QUERYTRACEON" is available. This option lets you to enable a plan-affecting trace flag only during single-query compilation. Like other query-level options, you can use it together with plan guides to match the text of a query being executed from any session, and automatically apply a plan-affecting trace flag when this query is being compiled.

Back to the top | Give Feedback

More information

The QUERYTRACEON hint is available as a query hint that enables a plan-affecting change in the query optimizer that is controlled by a trace flag. The QUERYTRACEON hint is specified as part of the OPTION clause similar to other query hints

(http://msdn.microsoft.com/en-us/library/ms181714.aspx)

Syntax

<querytraceon_hint> ::=
       { QUERYTRACEON trace_flag_number }

Arguments

QUERYTRACEON trace_flag_number

This specifies a plan-affecting trace flag number that is enabled during compiling of the query. The following trace flag numbers are supported:

Collapse this tableExpand this table

Trace flag	Microsoft Knowledge Base article	Available in
4199	974006 (http://support.microsoft.com/kb/974006/ )	Cumulative Update 6 for SQL Server 2005 Service Pack 3; Cumulative Update 7 for SQL Server 2008; Cumulative Update 7 for SQL Server 2008 Service Pack 1; SQL Server 2008 R2 and later versions.
All trace flags covered by 4199	974006 (http://support.microsoft.com/kb/974006/ )	Cumulative Update 6 for SQL Server 2005 Service Pack 3; Cumulative Update 7 for SQL Server 2008; Cumulative Update 7 for SQL Server 2008 Service Pack 1; SQL Server 2008 R2 and later versions.
2335	2413549 (http://support.microsoft.com/kb/2413549/ )	SQL Server 2005 and later versions.
2340	2009160 (http://support.microsoft.com/kb/2009160/ )	SQL Server 2005 and later versions.
2389, 2390	None	SQL Server 2005 and later versions. For a known issue in SQL Server 2005 environments please see 929278. (http://support.microsoft.com/kb/929278/en-us/ )
4136	980653 (http://support.microsoft.com/kb/980653/ )	Cumulative Update 9 for SQL Server 2005 Service Pack 3; Cumulative Update 7 for SQL Server 2008 Service Pack 1; Cumulative Update 2 for SQL Server 2008 R2 and later versions.
4137	2658214 (http://support.microsoft.com/kb/2658214/ )	Cumulative Update 8 for SQL Server 2008 Service Pack 2; Cumulative Update 7 for SQL Server 2008 Service Pack 3; Cumulative Update 5 for SQL Server 2008 R2 Service Pack 1; Cumulative Update 1 for SQL Server 2012 and later versions.
4138	2667211 (http://support.microsoft.com/kb/2667211/ )	Cumulative Update 13 for SQL Server 2008 R2; Cumulative Update 7 for SQL Server 2008 R2 Service Pack 1; Cumulative Update 1 for SQL Server 2008 R2 Service Pack 2; Cumulative Update 2 for SQL Server 2012 and later versions..

Remarks

The QUERYTRACEON option is not supported for trace flags other than the trace flags that are listed in the table. However, this option will not return any error or warning if an unsupported trace flag number is used. If the specified trace flag is not one that affects a query execution plan, the option will be silently ignored.

More than one trace flag can be specified in the OPTION clause if QUERYTRACEON trace_flag_number is duplicated with different trace flag numbers.

The QUERYTRACEON option can be used in Plan Guides

(http://msdn.microsoft.com/en-us/library/ms190417.aspx)

Back to the top | Give Feedback

Examples

You can enable all plan-affecting hotfixes controlled by trace flag 4199 for a particular query. For example, you can use the following query:
SELECT x FROM correlated WHERE f1 = 0 and f2 = 1 OPTION (QUERYTRACEON 4199)
You can enable all plan-affecting hotfixes controlled by trace flags 4199 and 4137 for a particular query. For example, you can use the following query:
SELECT x FROM correlated WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199, QUERYTRACEON 4137)

Back to the top | Give Feedback