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

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

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.

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.

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 articleAvailable in
4199974006Cumulative 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 4199974006Cumulative 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.
23352413549SQL Server 2005 and later versions.
23402009160SQL Server 2005 and later versions.
2389, 2390NoneSQL Server 2005 and later versions. For a known issue in SQL Server 2005 environments please see 929278.
4136980653Cumulative 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.
41372658214Cumulative 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.
41382667211Cumulative 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 Community Technology Preview 1 (CTP1) and Microsoft SQL Server 2014 Community Technology Preview 2 (CTP2):

Collapse this tableExpand this table
Trace FlagDescription
9481Use 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.
2312Use 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. 

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.

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)

Properties

Article ID: 2801413 - Last Review: January 6, 2014 - Revision: 3.0
Applies to
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2014 Enterprise Community Technology Preview 2
  • Microsoft SQL Server 2014 Standard Community Technology Preview 2
Keywords: 
kbinfo kbsurveynew kbexpertiseinter kbhowto KB2801413

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