Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level
Introduction
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
Syntax
<querytraceon_hint> ::=
{ QUERYTRACEON trace_flag_number }
Arguments
QUERYTRACEON trace_flag_numberThis specifies a plan-affecting trace flag number that is enabled during compiling of the query. The following trace flag numbers are supported:
| Trace flag | Microsoft Knowledge Base article | Available in |
|---|---|---|
| 4199 | 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 | 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 | SQL Server 2005 and later versions. |
| 2340 | 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. |
| 4136 | 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 | 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 | 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:
| Trace Flag | Description |
|---|---|
| 9481 | 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. |
| 2312 | 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. |
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.
Executing a query with the QUERYTRACEON option requires membership in the sysadmin fixed server role.
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)
No hay comentarios.:
Publicar un comentario