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.
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.
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.
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..
The following plan affecting trace flags are available in Microsoft SQL Server 2014:
Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.
Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 version) of the cardinality estimator when creating the query plan.
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.
Executing a query with the QUERYTRACEON option requires membership in the sysadmin fixed server role.