提示 (Transact-SQL) - 查询Hints (Transact-SQL) - Query

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

查询提示指定应在整个查询中使用指示的提示。Query hints specify that the indicated hints should be used throughout the query. 它们影响语句中的所有运算符。They affect all operators in the statement. 如果主查询中涉及 UNION,则只有最后一个涉及 UNION 运算符的查询可以包含 OPTION 子句。If UNION is involved in the main query, only the last query involving a UNION operation can have the OPTION clause. 查询提示作为 OPTION 子句的一部分指定。Query hints are specified as part of the OPTION clause. 如果一个或多个查询提示导致查询优化器生成无效计划,便会导致错误 8622 生成。Error 8622 occurs if one or more query hints cause the Query Optimizer not to generate a valid plan.

注意

由于 SQL ServerSQL Server 查询优化器通常会为查询选择最佳执行计划,我们建议资深开发人员和数据库管理员仅在不得已时使用提示。Because the SQL ServerSQL Server Query Optimizer typically selects the best execution plan for a query, we recommend only using hints as a last resort for experienced developers and database administrators.

适用范围:Applies to:

DELETEDELETE

INSERTINSERT

SELECTSELECT

UPDATEUPDATE

MERGEMERGE

语法Syntax

<query_hint > ::=   
{ { HASH | ORDER } GROUP   
  | { CONCAT | HASH | MERGE } UNION   
  | { LOOP | MERGE | HASH } JOIN   
  | EXPAND VIEWS   
  | FAST number_rows   
  | FORCE ORDER   
  | { FORCE | DISABLE } EXTERNALPUSHDOWN  
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX  
  | KEEP PLAN   
  | KEEPFIXED PLAN  
  | MAX_GRANT_PERCENT = percent  
  | MIN_GRANT_PERCENT = percent  
  | MAXDOP number_of_processors   
  | MAXRECURSION number   
  | NO_PERFORMANCE_SPOOL   
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )  
  | OPTIMIZE FOR UNKNOWN  
  | PARAMETERIZATION { SIMPLE | FORCED }   
  | RECOMPILE  
  | ROBUST PLAN   
  | USE HINT ( '<hint_name>' [ , ...n ] )
  | USE PLAN N'xml_plan'  
  | TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )  
}  
  
<table_hint> ::=  
[ NOEXPAND ] {   
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )  
  | FORCESEEK [( index_value ( index_column_name [,... ] ) ) ]  
  | FORCESCAN  
  | HOLDLOCK   
  | NOLOCK   
  | NOWAIT  
  | PAGLOCK   
  | READCOMMITTED   
  | READCOMMITTEDLOCK   
  | READPAST   
  | READUNCOMMITTED   
  | REPEATABLEREAD   
  | ROWLOCK   
  | SERIALIZABLE   
  | SNAPSHOT  
  | SPATIAL_WINDOW_MAX_CELLS = integer  
  | TABLOCK   
  | TABLOCKX   
  | UPDLOCK   
  | XLOCK  
}  

参数Arguments

{ HASH | ORDER } GROUP{ HASH | ORDER } GROUP
指定查询的 GROUP BY 或 DISTINCT 子句描述的聚合应使用哈希或排序。Specifies that aggregations that the query's GROUP BY or DISTINCT clause describes should use hashing or ordering.

{ MERGE | HASH | CONCAT } UNION{ MERGE | HASH | CONCAT } UNION
指定所有 UNION 运算都通过合并、哈希或串联 UNION 集来运行。Specifies that all UNION operations are run by merging, hashing, or concatenating UNION sets. 如果指定了多个 UNION 提示,查询优化器就会从这些指定的提示中选择开销最少的策略。If more than one UNION hint is specified, the Query Optimizer selects the least expensive strategy from those hints specified.

{ LOOP | MERGE | HASH } JOIN{ LOOP | MERGE | HASH } JOIN
指定所有联接操作都通过整个查询中的 LOOP JOIN、MERGE JOIN 或 HASH JOIN 执行。Specifies all join operations are performed by LOOP JOIN, MERGE JOIN, or HASH JOIN in the whole query. 如果你指定了多个联接提示,优化器从可使用的联接策略中选择支出最低的。If you specify more than one join hint, the optimizer selects the least expensive join strategy from the allowed ones.

如果在同一查询对特定表对的 FROM 子句中指定联接提示,那么在联接两个表时优先使用此联接提示。If you specify a join hint in the same query's FROM clause for a specific table pair, this join hint takes precedence in the joining of the two tables. 不过,仍必须遵循查询提示。The query hints, though, must still be honored. 面向表对的联接提示可能只限制在查询提示中选择可用的联接方法。The join hint for the pair of tables may only restrict the selection of allowed join methods in the query hint. 有关详细信息,请参阅联接提示 (Transact-SQL)For more information, see Join Hints (Transact-SQL).

EXPAND VIEWSEXPAND VIEWS
指定展开索引视图。Specifies the indexed views are expanded. 此外,还指定查询优化器不将任何索引视图视为任何查询部分的替代。Also specifies the Query Optimizer won't consider any indexed view as a replacement for any query part. 当查询文本中的视图名称被视图定义替换时,视图展开。A view is expanded when the view definition replaces the view name in the query text.

实际上,该查询提示不允许在查询计划中直接使用索引视图和直接在索引视图上使用索引。This query hint virtually disallows direct use of indexed views and indexes on indexed views in the query plan.

如果在查询的 SELECT 部分中有对视图的直接引用,索引视图继续处于紧缩状态。The indexed view remains condensed if there's a direct reference to the view in the query's SELECT part. 如果你指定 WITH (NOEXPAND) 或 WITH (NOEXPAND, INDEX(index_value_ [ , ...n ] ) ),视图也继续处于紧缩状态。The view also remains condensed if you specify WITH (NOEXPAND) or WITH (NOEXPAND, INDEX(index_value_ [ ,...n ] ) ). 有关查询提示 NOEXPAND 的详细信息,请参阅使用 NOEXPANDFor more information about the query hint NOEXPAND, see Using NOEXPAND.

提示只影响语句的 SELECT 部分中的视图(包括 INSERT、UPDATE、MERGE 和 DELETE 语句中的视图)。The hint only affects the views in the statements' SELECT part, including those views in INSERT, UPDATE, MERGE, and DELETE statements.

FAST number_rows FAST number_rows
指定优化查询,以便快速检索前 number_rows 行。Specifies that the query is optimized for fast retrieval of the first number_rows. 此结果是非负整数。This result is a nonnegative integer. 在前 number_rows 行返回后,查询继续执行,并生成完整结果集。After the first number_rows are returned, the query continues execution and produces its full result set.

FORCE ORDERFORCE ORDER
指定在查询优化过程中保持由查询语法指示的联接顺序。Specifies that the join order indicated by the query syntax is preserved during query optimization. 使用 FORCE ORDER 不影响查询优化器可能出现的角色逆转行为。Using FORCE ORDER doesn't affect possible role reversal behavior of the Query Optimizer.

备注

在 MERGE 语句中,如果未指定 WHEN SOURCE NOT MATCHED 子句,则按照默认的联接次序,先访问源表再访问目标表。In a MERGE statement, the source table is accessed before the target table as the default join order, unless the WHEN SOURCE NOT MATCHED clause is specified. 如果指定 FORCE ORDER,则保留此默认行为。Specifying FORCE ORDER preserves this default behavior.

{ FORCE | DISABLE } EXTERNALPUSHDOWN{ FORCE | DISABLE } EXTERNALPUSHDOWN
强制或禁用向下推送 Hadoop 中符合条件的表达式的计算。Force or disable the pushdown of the computation of qualifying expressions in Hadoop. 仅适用于使用 PolyBase 的查询。Only applies to queries using PolyBase. 不会向下推送到 Azure 存储。Won't push down to Azure storage.

KEEP PLANKEEP PLAN
强制查询优化器对查询放宽估计的重新编译阈值。Forces the Query Optimizer to relax the estimated recompile threshold for a query. 通过运行以下语句之一对表进行了估计数目的索引列更改后,估计的重新编译阈值会开始自动重新编译查询:The estimated recompile threshold starts an automatic recompile for the query when the estimated number of indexed column changes have been made to a table by running one of the following statements:

  • UPDATEUPDATE
  • 删除DELETE
  • MERGEMERGE
  • InsertINSERT

指定 KEEP PLAN 可确保不会像表有多个更新一样频繁地重新编译查询。Specifying KEEP PLAN makes sure a query won't be recompiled as frequently when there are multiple updates to a table.

KEEPFIXED PLANKEEPFIXED PLAN
强制查询优化器不因统计信息变化而重新编译查询。Forces the Query Optimizer not to recompile a query because of changes in statistics. 指定 KEEPFIXED PLAN 可确保仅当更改基础表的架构或对这些表运行 sp_recompile 时才重新编译查询。Specifying KEEPFIXED PLAN makes sure that a query recompiles only if the schema of the underlying tables changes or if sp_recompile runs against those tables.

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEXIGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
适用于SQL ServerSQL Server(从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,以及更高版本。Applies to: SQL ServerSQL Server (staring with SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

防止查询使用非聚集内存优化列存储索引。Prevents the query from using a nonclustered memory optimized columnstore index. 如果查询包含避免使用列存储索引的查询提示,而又包含支持使用列存储索引的索引提示,那么这两个提示相互冲突,导致查询返回错误。If the query contains the query hint to avoid the use of the columnstore index, and an index hint to use a columnstore index, the hints are in conflict and the query returns an error.

MAX_GRANT_PERCENT = percent MAX_GRANT_PERCENT = percent
适用范围:SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and Azure SQL DatabaseAzure SQL Database.

内存授予大小所占的最大百分比。The maximum memory grant size in PERCENT. 查询保证不会超过此限制。The query is guaranteed not to exceed this limit. 如果 Resource Governor 设置低于此提示指定的值,则实际限制可能更低。The actual limit can be lower if the Resource Governor setting is lower than the value specified by this hint. 有效值介于 0.0 和 100.0 之间。Valid values are between 0.0 and 100.0.

MIN_GRANT_PERCENT = percent MIN_GRANT_PERCENT = percent
适用范围:SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and Azure SQL DatabaseAzure SQL Database.

内存授予大小所占的最小百分比 = 默认百分比限制。The minimum memory grant size in PERCENT = % of default limit. 查询保证会获取最大值(必需内存,最小授予),因为至少需要必需内存才能启动查询。The query is guaranteed to get MAX(required memory, min grant) because at least required memory is needed to start a query. 有效值介于 0.0 和 100.0 之间。Valid values are between 0.0 and 100.0.

MAXDOP number MAXDOP number
适用范围:SQL ServerSQL Server(从 SQL Server 2008SQL Server 2008 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database.

替代 sp_configure 的“最大并行度” 配置选项。Overrides the max degree of parallelism configuration option of sp_configure. 还会替代指定此选项的查询 Resource Governor。Also overrides the Resource Governor for the query specifying this option. MAXDOP 查询提示可以超出使用 sp_configure 配置的值。The MAXDOP query hint can exceed the value configured with sp_configure. 如果 MAXDOP 超出使用 Resource Governor 配置的值,则数据库引擎Database Engine会使用 Resource Governor MAXDOP 值(如 ALTER WORKLOAD GROUP (Transact-SQL) 中所述)。If MAXDOP exceeds the value configured with Resource Governor, the 数据库引擎Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). 使用 MAXDOP 查询提示时,所有和 max degree of parallelism 配置选项一起使用的语义规则均适用 。All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. 有关详细信息,请参阅 配置 max degree of parallelism 服务器配置选项For more information, see Configure the max degree of parallelism Server Configuration Option.

警告

如果 MAXDOP 设置为零,服务器将选择最大并行度。If MAXDOP is set to zero, then the server chooses the max degree of parallelism.

MAXRECURSION number MAXRECURSION number
指定该查询允许的最大递归数。Specifies the maximum number of recursions allowed for this query. number 是介于 0 至 32,767 之间的非负整数 。number is a nonnegative integer between 0 and 32,767. 如果指定 0,则没有限制。When 0 is specified, no limit is applied. 如果未指定此选项,服务器的默认限制为 100。If this option isn't specified, the default limit for the server is 100.

如果在查询执行期间达到指定或默认的 MAXRECURSION 数量限制,查询结束并返回错误。When the specified or default number for MAXRECURSION limit is reached during query execution, the query ends and an error returns.

由于此错误,该语句的所有结果都被回滚。Because of this error, all effects of the statement are rolled back. 如果该语句为 SELECT 语句,则可能会返回部分结果或不返回结果。If the statement is a SELECT statement, partial results or no results may be returned. 所返回的任何部分结果都可能无法包括超过指定最大递归级别的递归级别上的所有行。Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.

有关详细信息,请参阅 WITH common_table_expression (Transact-SQL)For more information, see WITH common_table_expression (Transact-SQL).

NO_PERFORMANCE_SPOOLNO_PERFORMANCE_SPOOL
适用范围:SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and Azure SQL DatabaseAzure SQL Database.

防止将 spool 运算符添加到查询计划(需要 spool 保证更新语义有效的计划除外)。Prevents a spool operator from being added to query plans (except for the plans when spool is required to guarantee valid update semantics). 在某些情况下,spool 运算符可能会降低性能。The spool operator may reduce performance in some scenarios. 例如,如果有大量查询与 spool 操作并发运行,spool 将使用 tempdb 并会出现 tempdb 争用。For example, the spool uses tempdb, and tempdb contention can occur if there are many concurrent queries running with the spool operations.

OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
在编译和优化查询时指示查询优化器对局部变量使用特定值。Instructs the Query Optimizer to use a particular value for a local variable when the query is compiled and optimized. 仅在查询优化期间使用该值,在查询执行期间不使用该值。The value is used only during query optimization, and not during query execution.

@variable_name@variable_name
在查询中使用的局部变量的名称,可以为其分配用于 OPTIMIZE FOR 查询提示的值。Is the name of a local variable used in a query, to which a value may be assigned for use with the OPTIMIZE FOR query hint.

UNKNOWN UNKNOWN
指定查询优化器在查询优化期间使用统计数据而不是初始值来确定局部变量的值。Specifies that the Query Optimizer uses statistical data instead of the initial value to determine the value for a local variable during query optimization.

literal_constant literal_constant
要分配给 @variable_name 的文本常量值,以用于 OPTIMIZE FOR 查询提示。Is a literal constant value to be assigned @variable_name for use with the OPTIMIZE FOR query hint. literal_constant 只在查询优化期间使用,在查询执行期间不用作 @variable_name 的值。literal_constant is used only during query optimization, and not as the value of @variable_name during query execution. literal_constant 可以是任意可表达为文本常量的 SQL ServerSQL Server 系统数据类型。literal_constant can be of any SQL ServerSQL Server system data type that can be expressed as a literal constant. literal_constant 的数据类型必须可隐式转换为 @variable_name 在查询中引用的数据类型。The data type of literal_constant must be implicitly convertible to the data type that @variable_name references in the query.

OPTIMIZE FOR 可能会对优化器的默认参数检测行为起反作用。OPTIMIZE FOR can counteract the optimizer's default parameter detection behavior. 创建计划指南时,也使用 OPTIMIZE FOR。Also use OPTIMIZE FOR when you create plan guides. 有关详细信息,请参阅重新编译存储过程For more information, see Recompile a Stored Procedure.

OPTIMIZE FOR UNKNOWNOPTIMIZE FOR UNKNOWN
指示查询优化器在编译和优化查询时,对所有本地变量使用统计数据,而不是初始值。Instructs the Query Optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized. 此优化包括使用强制参数化创建的参数。This optimization includes parameters created with forced parameterization.

如果在同一查询提示中使用 OPTIMIZE FOR @variable_name = literal_constant 和 OPTIMIZE FOR UNKNOWN,查询优化器会使用为特定值指定的 literal_constantIf you use OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN in the same query hint, the Query Optimizer will use the literal_constant specified for a specific value. 查询优化器会对其余变量值使用 UNKNOWN。The Query Optimizer will use UNKNOWN for the rest of the variable values. 这些值仅用于查询优化期间,而不会用于查询执行期间。The values are used only during query optimization, and not during query execution.

PARAMETERIZATION { SIMPLE | FORCED }PARAMETERIZATION { SIMPLE | FORCED }
指定在编译查询时 SQL ServerSQL Server 查询优化器应用于查询的参数化规则。Specifies the parameterization rules that the SQL ServerSQL Server Query Optimizer applies to the query when it's compiled.

重要

只能在计划指南中指定 PARAMETERIZATION 查询提示,用于覆盖 PARAMETERIZATION 数据库 SET 选项的当前设置。The PARAMETERIZATION query hint can only be specified inside a plan guide to override the current setting of the PARAMETERIZATION database SET option. 无法直接在查询中指定它。It can't be specified directly within a query.
有关详细信息,请参阅使用计划指南指定查询参数化行为For more information, see Specify Query Parameterization Behavior by Using Plan Guides.

SIMPLE 用于指示查询优化器尝试进行简单参数化。SIMPLE instructs the Query Optimizer to attempt simple parameterization. FORCED 用于指示查询优化器尝试进行强制参数化。FORCED instructs the Query Optimizer to attempt forced parameterization. 有关详细信息,请参阅查询处理体系结构指南中的强制参数化查询处理体系结构指南中的简单参数化For more information, see Forced Parameterization in the Query Processing Architecture Guide, and Simple Parameterization in the Query Processing Architecture Guide.

RECOMPILERECOMPILE
指示 SQL Server 数据库引擎SQL Server Database Engine 为查询生成新的临时计划,并在查询完成执行后立即放弃该计划。Instructs the SQL Server 数据库引擎SQL Server Database Engine to generate a new, temporary plan for the query and immediately discard that plan after the query completes execution. 如果在未指定 RECOMPILE 提示的情况下运行同一查询,生成的查询计划不会替换缓存中存储的计划。The generated query plan doesn't replace a plan stored in cache when the same query runs without the RECOMPILE hint. 如果未指定 RECOMPILE,数据库引擎Database Engine将缓存查询计划并重新使用它们。Without specifying RECOMPILE, the 数据库引擎Database Engine caches query plans and reuses them. 编译查询计划时,RECOMPILE 查询提示使用查询中任何本地变量的当前值。When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query. 如果查询在存储过程内,当前值会传递给任意参数。If the query is inside a stored procedure, the current values passed to any parameters.

RECOMPILE 是创建存储过程的实用替代方法。RECOMPILE is a useful alternative to creating a stored procedure. 如果必须仅重新编译存储过程中的一部分查询,而不是重新编译整个存储过程,RECOMPILE 使用 WITH RECOMPILE 子句。RECOMPILE uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. 有关详细信息,请参阅重新编译存储过程For more information, see Recompile a Stored Procedure. 在创建计划指南时,RECOMPILE 也很有用。RECOMPILE is also useful when you create plan guides.

ROBUST PLANROBUST PLAN
强制查询优化器尝试一个计划,该计划可能以性能为代价获得可能的最大行大小。Forces the Query Optimizer to try a plan that works for the maximum potential row size, possibly at the expense of performance. 处理查询时,中间表和运算符可能需要存储和处理比任一输入行宽的行。When the query is processed, intermediate tables and operators may have to store and process rows that are wider than any one of the input rows when the query is processed. 有时,行可能很宽,导致特定运算符无法处理行。The rows may be so wide that, sometimes, the particular operator can't process the row. 如果行这么宽,数据库引擎Database Engine会在查询执行期间生成错误。If rows are that wide, the 数据库引擎Database Engine produces an error during query execution. 通过使用 ROBUST PLAN,可以指示查询优化器不考虑所有可能会遇到此问题的查询计划。By using ROBUST PLAN, you instruct the Query Optimizer not to consider any query plans that may run into this problem.

如果此类计划不可行,则查询优化器会返回错误,而不是将错误检测延迟到查询执行阶段。If such a plan isn't possible, the Query Optimizer returns an error instead of deferring error detection to query execution. 行可以包含可变长度列;数据库引擎Database Engine允许将行大小定义为超过数据库引擎Database Engine处理能力的最大可能的大小。Rows may contain variable-length columns; the 数据库引擎Database Engine allows for rows to be defined that have a maximum potential size beyond the ability of the 数据库引擎Database Engine to process them. 通常,应用程序存储实际大小在数据库引擎Database Engine处理能力范围内的行,而不管最大可能大小。Generally, despite the maximum potential size, an application stores rows that have actual sizes within the limits that the 数据库引擎Database Engine can process. 如果数据库引擎Database Engine遇到过长的行,便会返回执行错误。If the 数据库引擎Database Engine comes across a row that is too long, an execution error is returned.

USE HINT ( ' hint_name ' )USE HINT ( 'hint_name' )
适用范围 :SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1) and Azure SQL DatabaseAzure SQL Database.

向查询处理器提供一个或多个附加提示。Provides one or more additional hints to the query processor. 附加提示由提示名称(放在单引号内) 进行指定。The additional hints are specified by a hint name inside single quotation marks.

支持以下提示名称:The following hint names are supported:

  • 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS' 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
    SQL Server 2014 (12.x)SQL Server 2014 (12.x) 或更新版本的查询优化器基数估计模型下,导致 SQL ServerSQL Server 使用联接的简单包含假设而非默认的基本包含假设来生成查询计划。Causes SQL ServerSQL Server to generate a query plan using the Simple Containment assumption instead of the default Base Containment assumption for joins, under the Query Optimizer Cardinality Estimation model of SQL Server 2014 (12.x)SQL Server 2014 (12.x) or newer. 此提示名与跟踪标志 9476 等效。This hint name is equivalent to trace flag 9476.

  • 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES' 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
    会导致 SQL ServerSQL Server 在为了进行关联而对筛选的 AND 谓词进行估值时使用最小选择性来生成计划。Causes SQL ServerSQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更早版本的基数估计模型一起使用时,此提示名等效于跟踪标志 4137;与 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 或更高版本的基数估计模型一起使用时,它等效于跟踪标志 9471。This hint name is equivalent to trace flag 4137 when used with cardinality estimation model of SQL Server 2012 (11.x)SQL Server 2012 (11.x) and earlier versions, and has similar effect when trace flag 9471 is used with cardinality estimation model of SQL Server 2014 (12.x)SQL Server 2014 (12.x) or higher.

  • 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS''DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
    禁用批处理模式自适应联接。Disables batch mode adaptive joins. 有关详细信息,请参阅批处理模式自适应联接For more information, see Batch mode Adaptive Joins.
    适用范围:SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database.

  • 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK''DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
    禁用批处理模式内存授予反馈。Disables batch mode memory grant feedback. 有关详细信息,请参阅批处理模式内存授予反馈For more information, see Batch mode memory grant feedback.
    适用范围:SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database.

  • 'DISABLE_DEFERRED_COMPILATION_TV''DISABLE_DEFERRED_COMPILATION_TV'
    禁用表变量延迟编译。Disables table variable deferred compilation. 有关详细信息,请参阅表变量延迟编译For more information, see Table variable deferred compilation.
    适用范围:SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database.

  • 'DISABLE_INTERLEAVED_EXECUTION_TVF''DISABLE_INTERLEAVED_EXECUTION_TVF'
    对多语句表值函数禁用交错执行。Disables interleaved execution for multi-statement table-valued functions. 有关详细信息,请参阅多语句表值函数的交错执行For more information, see Interleaved execution for multi-statement table-valued functions.
    适用范围:SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database.

  • 'DISABLE_OPTIMIZED_NESTED_LOOP''DISABLE_OPTIMIZED_NESTED_LOOP'
    指示查询处理器在生成查询计划时不对优化的嵌套循环联接使用排序操作(批处理排序)。Instructs the query processor not to use a sort operation (batch sort) for optimized nested loop joins when generating a query plan. 此提示名与跟踪标志 2340 等效。This hint name is equivalent to trace flag 2340.

  • 'DISABLE_OPTIMIZER_ROWGOAL' 'DISABLE_OPTIMIZER_ROWGOAL'
    让 SQL Server 生成计划,它不对包含以下关键字的查询使用行目标修改:Causes SQL Server to generate a plan that doesn't use row goal modifications with queries that contain these keywords:

    • 返回页首TOP
    • OPTION (FAST N)OPTION (FAST N)
    • ININ
    • EXISTSEXISTS

    此提示名与跟踪标志 4138 等效。This hint name is equivalent to trace flag 4138.

  • 'DISABLE_PARAMETER_SNIFFING''DISABLE_PARAMETER_SNIFFING'
    指示查询优化器在使用一个或多个参数编译查询时,使用平均数据分布。Instructs Query Optimizer to use average data distribution while compiling a query with one or more parameters. 此指令让查询计划独立于编译查询时首次使用的参数值。This instruction makes the query plan independent on the parameter value that was first used when the query was compiled. 此提示名与跟踪标志 4136 或数据库作用域配置设置 PARAMETER_SNIFFING = OFF 等效。This hint name is equivalent to trace flag 4136 or Database Scoped Configuration setting PARAMETER_SNIFFING = OFF.

  • 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK''DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
    禁用行模式内存授予反馈。Disables row mode memory grant feedback. 有关详细信息,请参阅模式内存授予反馈For more information, see Row mode memory grant feedback.
    适用范围:SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database.

  • 'DISABLE_TSQL_SCALAR_UDF_INLINING''DISABLE_TSQL_SCALAR_UDF_INLINING'
    禁用标量 UDF 内联。Disables scalar UDF inlining. 有关详细信息,请参阅标量 UDF 内联For more information, see Scalar UDF Inlining.
    适用范围:SQL ServerSQL Server(从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始) 。Applies to: SQL ServerSQL Server (starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)).

  • 'DISALLOW_BATCH_MODE''DISALLOW_BATCH_MODE'
    禁用批处理模式执行。Disables batch mode execution. 有关详细信息,请参阅执行模式For more information, see Execution modes.
    适用范围:SQL ServerSQL Server(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database.

  • 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS''ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
    针对需要对其执行基数估计的任何前导索引列,启用自动生成的快速统计信息(直方图修正)。Enables automatically generated quick statistics (histogram amendment) for any leading index column for which cardinality estimation is needed. 用于评估基数的直方图将在查询编译时针对此列的实际最大值或最小值进行调整。The histogram used to estimate cardinality will be adjusted at query compile time to account for actual maximum or minimum value of this column. 此提示名与跟踪标志 4139 等效。This hint name is equivalent to trace flag 4139.

  • 'ENABLE_QUERY_OPTIMIZER_HOTFIXES''ENABLE_QUERY_OPTIMIZER_HOTFIXES'
    启用查询优化器修补程序(SQL Server 累积更新和服务包中发布的更改)。Enables Query Optimizer hotfixes (changes released in SQL Server Cumulative Updates and Service Packs). 此提示名与跟踪标志 4199 或数据库作用域配置设置 QUERY_OPTIMIZER_HOTFIXES = ON 等效。This hint name is equivalent to trace flag 4199 or Database Scoped Configuration setting QUERY_OPTIMIZER_HOTFIXES = ON.

  • 'FORCE_DEFAULT_CARDINALITY_ESTIMATION''FORCE_DEFAULT_CARDINALITY_ESTIMATION'
    强制查询优化器使用与当前数据库兼容级别相对应的基数估计模型。Forces the Query Optimizer to use Cardinality Estimation model that corresponds to the current database compatibility level. 使用此提示替代数据库作用域配置设置 LEGACY_CARDINALITY_ESTIMATION = ON跟踪标志 9481。Use this hint to override Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION = ON or trace flag 9481.

  • 'FORCE_LEGACY_CARDINALITY_ESTIMATION' 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
    强制查询优化器使用 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更早版本的基数估计模型。Forces the Query Optimizer to use Cardinality Estimation model of SQL Server 2012 (11.x)SQL Server 2012 (11.x) and earlier versions. 此提示名与跟踪标志 9481 或数据库作用域配置设置 LEGACY_CARDINALITY_ESTIMATION = ON 等效。This hint name is equivalent to trace flag 9481 or Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION = ON.

  • 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
    强制查询优化器行为在查询级别发生。Forces the Query Optimizer behavior at a query level. 此行为就像使用数据库兼容性级别 n 编译查询(其中,n 是受支持的数据库兼容性级别)。This behavior happens as if the query was compiled with database compatibility level n, where n is a supported database compatibility level. 请参阅 sys.dm_exec_valid_use_hints,以了解目前对 n 支持的值。Refer to sys.dm_exec_valid_use_hints for a list of currently supported values for n.
    适用范围SQL ServerSQL Server(自 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU10 起)。Applies to: SQL ServerSQL Server (starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU10).

    备注

    QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 提示不替代默认或旧版基数估计设置(如果它是通过数据库范围内配置、跟踪标志或 QUERYTRACEON 等其他查询提示强制执行的话)。The QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n hint doesn't override default or legacy cardinality estimation setting, if it's forced through database scoped configuration, trace flag or another query hint such as QUERYTRACEON.
    此提示仅影响查询优化器的行为。This hint only affects the behavior of the Query Optimizer. 它不影响可能依赖数据库兼容性级别的其他 SQL ServerSQL Server 功能(如某些数据库功能的可用性)。It doesn't affect other features of SQL ServerSQL Server that may depend on the database compatibility level, such as the availability of certain database features.
    若要了解有关此提示的详细信息,请参阅Developer's Choice:Hinting Query Execution model(开发人员的选择:提示查询执行模型)。To learn more about this hint, see Developer's Choice: Hinting Query Execution model.

  • 'QUERY_PLAN_PROFILE''QUERY_PLAN_PROFILE'
    启用用于查询的轻型分析。Enables lightweight profiling for the query. 当包含此新提示的查询完成时,会触发一个新扩展事件:query_plan_profile。When a query that contains this new hint finishes, a new Extended Event, query_plan_profile, is fired. 此扩展事件公开执行统计信息和实际执行计划 XML,类似于 query_post_execution_showplan 扩展事件,但仅针对包含新提示的查询。This extended event exposes execution statistics and actual execution plan XML similar to the query_post_execution_showplan extended event but only for queries that contains the new hint.
    适用范围:SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11 开始)。Applies to: SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11).

    备注

    如果启用收集 query_post_execution_showplan 扩展事件,会向在服务器上运行的每个查询添加标准分析基础结构,因此可能会影响服务器的总体性能。If you enable collecting the query_post_execution_showplan extended event, this will add standard profiling infrastructure to every query that is running on the server and therefore may affect overall server performance.
    如果启用 query_thread_profile 扩展事件集合以改为使用轻量分析基础结构,这将在很大程度上减少性能开销,但仍会影响服务器的总体性能。If you enable the collection of query_thread_profile extended event to use lightweight profiling infrastructure instead, this will result in much less performance overhead but will still affect overall server performance.
    如果启用 query_plan_profile 扩展事件,将只启用通过 QUERY_PLAN_PROFILE 执行的查询的轻量分析基础结构,因此不会影响服务器上的其他工作负载。If you enable the query_plan_profile extended event, this will only enable the lightweight profiling infrastructure for a query that executed with the QUERY_PLAN_PROFILE and therefore will not affect other workloads on the server. 使用此提示来分析特定查询,而不会影响服务器工作负载的其他部分。Use this hint to profile a specific query without affecting other parts of the server workload. 若要了解有关此轻量分析的详细信息,请参阅查询分析基础结构To learn more about lightweight profiling, see Query Profiling Infrastructure.

可以使用动态管理视图 sys.dm_exec_valid_use_hints 查询所有受支持的 USE HINT 名称的列表。The list of all supported USE HINT names can be queried using the dynamic management view sys.dm_exec_valid_use_hints.

提示

提示名称不区分大小写。Hint names are case-insensitive.

重要

某些 USE HINT 提示可能与在全局或会话级别启用的跟踪标志或与数据库作用域配置设置存在冲突。Some USE HINT hints may conflict with trace flags enabled at the global or session level, or database scoped configuration settings. 在这种情况下,查询级别提示 (USE HINT) 将始终优先。In this case, the query level hint (USE HINT) always takes precedence. 如果 USE HINT 与另一个查询提示或在查询级别启用(例如由 QUERYTRACEON 启用)的跟踪标志存在冲突,SQL ServerSQL Server 将在尝试执行查询时生成错误。If a USE HINT conflicts with another query hint, or a trace flag enabled at the query level (such as by QUERYTRACEON), SQL ServerSQL Server will generate an error when trying to execute the query.

USE PLAN N'xml_plan'USE PLAN N'xml_plan'
强制查询优化器为查询使用由 ' xml_plan ' 指定的现有查询计划。Forces the Query Optimizer to use an existing query plan for a query that is specified by 'xml_plan'. 不能使用 INSERT、UPDATE、MERGE 或 DELETE 语句来指定 USE PLAN。USE PLAN cannot be specified with INSERT, UPDATE, MERGE, or DELETE statements.

TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] ) 将指定表提示应用于与 exposed_object_name 对应的表或视图。TABLE HINT (exposed_object_name [ , <table_hint> [ [, ]...n ] ] ) Applies the specified table hint to the table or view that corresponds to exposed_object_name. 我们建议仅在计划指南的上下文中将表提示用作查询提示。We recommend using a table hint as a query hint only in the context of a plan guide.

exposed_object_name 可以是下面的引用之一:exposed_object_name can be one of the following references:

  • 如果在查询的 FROM 子句中对表或视图使用别名,exposed_object_name 就是别名。When an alias is used for the table or view in the FROM clause of the query, exposed_object_name is the alias.

  • 如果不使用别名,exposed_object_name 与 FROM 子句中引用的表或视图完全匹配。When an alias isn't used, exposed_object_name is the exact match of the table or view referenced in the FROM clause. 例如,如果使用包含两部分的名称引用表或视图,exposed_object_name 就是这个包含两部分的名称。For example, if the table or view is referenced using a two-part name, exposed_object_name is the same two-part name.

如果在指定 exposed_object_name 时未指定表提示,在查询中指定为属于对象的表提示的任何索引都会遭忽略。When you specify exposed_object_name without also specifying a table hint, any indexes you specify in the query as part of a table hint for the object are disregarded. 然后,查询优化器确定索引使用情况。The Query Optimizer then determines index usage. 如果无法修改原始查询,可以使用此方法来消除 INDEX 表提示的影响。You can use this technique to eliminate the effect of an INDEX table hint when you can't modify the original query. 请参阅示例 J。See Example J.

<table_hint> ::= { [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [( index_value ( index_column_name [, ... ] )) ]| FORCESCAN | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | SNAPSHOT | SPATIAL_WINDOW_MAX_CELLS | TABLOCK | TABLOCKX | UPDLOCK | XLOCK } 作为查询提示应用于与 exposed_object_name 对应的表或视图的表提示。<table_hint> ::= { [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [(index_value(index_column_name [,... ] )) ]| FORCESCAN | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | SNAPSHOT | SPATIAL_WINDOW_MAX_CELLS | TABLOCK | TABLOCKX | UPDLOCK | XLOCK } Is the table hint to apply to the table or view that corresponds to exposed_object_name as a query hint. 有关这些提示的说明,请参阅表提示 (Transact-SQL)For a description of these hints, see Table Hints (Transact-SQL).

不允许将非 INDEX、FORCESCAN 和 FORCESEEK 的表提示用作查询提示,除非该查询已经具有一个指定该表提示的 WITH 子句。Table hints other than INDEX, FORCESCAN, and FORCESEEK are disallowed as query hints unless the query already has a WITH clause specifying the table hint. 有关详细信息,请参阅“备注”。For more information, see Remarks.

注意

指定带参数的 FORCESEEK 限制优化器可以考虑的计划数大于指定不带参数的 FORCESEEK 时的计划数。Specifying FORCESEEK with parameters limits the number of plans that can be considered by the optimizer more than when specifying FORCESEEK without parameters. 这可能导致在更多情况下出现“无法生成计划”错误。This may cause a "Plan cannot be generated" error to occur in more cases. 在未来的版本中,对优化器进行内部修改后可允许考虑更多计划。In a future release, internal modifications to the optimizer may allow more plans to be considered.

RemarksRemarks

只有在 INSERT 语句中使用了 SELECT 子句时,才能在该语句中指定查询提示。Query hints cannot be specified in an INSERT statement, except when a SELECT clause is used inside the statement.

只能在顶级查询中指定查询提示,不能在子查询指定。Query hints can be specified only in the top-level query, not in subqueries. 将表提示指定为查询提示时,可以在顶级查询或子查询中指定提示。When a table hint is specified as a query hint, the hint can be specified in the top-level query or in a subquery. 不过,在 TABLE HINT 子句中为 exposed_object_name 指定的值必须与查询或子查询中的公开名称完全匹配。However, the value specified for exposed_object_name in the TABLE HINT clause must match exactly the exposed name in the query or subquery.

将表提示指定为查询提示Specifying Table Hints as Query Hints

我们建议仅在计划指南的上下文中将 INDEX、FORCESCAN 或 FORCESEEK 表提示用作查询提示。We recommend using the INDEX, FORCESCAN, or FORCESEEK table hint as a query hint only in the context of a plan guide. 如果无法修改原始查询(例如,由于它是第三方应用程序),就会发现计划指南很有用。Plan guides are useful when you can't modify the original query, for example, because it's a third-party application. 计划指南中指定的查询提示在查询编译和优化前添加到查询中。The query hint specified in the plan guide is added to the query before it's compiled and optimized. 对于即席查询,仅在测试计划指南语句时才应使用 TABLE HINT 子句。For ad-hoc queries, use the TABLE HINT clause only when testing plan guide statements. 对于所有其他即席查询,建议仅将这些提示指定为表提示。For all other ad-hoc queries, we recommend specifying these hints only as table hints.

如果将 INDEX、FORCESCAN 和 FORCESEEK 表提示指定为查询提示,它们会对以下对象有效:When specified as a query hint, the INDEX, FORCESCAN, and FORCESEEK table hints are valid for the following objects:

  • Tables
  • 视图Views
  • 索引视图Indexed views
  • 公用表表达式(必须在其结果集填充公用表表达式的 SELECT 语句中指定提示)Common table expressions (the hint must be specified in the SELECT statement whose result set populates the common table expression)
  • 动态管理视图Dynamic management views
  • 命名子查询Named subqueries

可以将 INDEX、FORCESCAN 和 FORCESEEK 表提示指定为,没有任何现有表提示的查询的查询提示。You can specify INDEX, FORCESCAN, and FORCESEEK table hints as query hints for a query that doesn't have any existing table hints. 还可以使用它们分别替换查询中的现有 INDEX、FORCESCAN 或 FORCESEEK 提示。You can also use them to replace existing INDEX, FORCESCAN, or FORCESEEK hints in the query, respectively.

不允许将非 INDEX、FORCESCAN 和 FORCESEEK 的表提示用作查询提示,除非该查询已经具有一个指定该表提示的 WITH 子句。Table hints other than INDEX, FORCESCAN, and FORCESEEK are disallowed as query hints unless the query already has a WITH clause specifying the table hint. 在这种情况下,还必须将匹配提示指定为查询提示。In this case, a matching hint must also be specified as a query hint. 在 OPTION 子句中使用 TABLE HINT,将匹配提示指定为查询提示。Specify the matching hint as a query hint by using TABLE HINT in the OPTION clause. 此规范保留了查询语义。This specification preserves the query's semantics. 例如,如果查询包含表提示 NOLOCK,则计划指南的 @hints 参数中的 OPTION 子句必须也包含 NOLOCK 提示 。For example, if the query contains the table hint NOLOCK, the OPTION clause in the @hints parameter of the plan guide must also contain the NOLOCK hint. 请参见示例 K。See Example K.

几种情况会导致错误 8072 生成。Error 8072 occurs in a couple of scenarios. 一种情况是,在 OPTION 子句中使用 TABLE HINT 指定除 INDEX、FORCESCAN 或 FORCESEEK 之外的表提示,而没有匹配的查询提示。One is when you specify a table hint other than INDEX, FORCESCAN, or FORCESEEK by using TABLE HINT in the OPTION clause without a matching query hint. 另一种情况则是反过来。The second scenario is the other way around. 此错误指明 OPTION 子句可能会导致查询语义改变,且查询失败。This error indicates the OPTION clause can cause the semantics of the query to change, and the query fails.

示例Examples

A.A. 使用 MERGE JOINUsing MERGE JOIN

下面的示例指定,MERGE JOIN 在查询中运行 JOIN 操作。The following example specifies that MERGE JOIN runs the JOIN operation in the query. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

SELECT *   
FROM Sales.Customer AS c  
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID  
WHERE TerritoryID = 5  
OPTION (MERGE JOIN);  
GO    

B.B. 使用 OPTIMIZE FORUsing OPTIMIZE FOR

以下示例指示查询优化器对局部变量 @city_name 使用值 'Seattle',并在优化查询时使用统计数据来确定局部变量 @postal_code 的值。The following example instructs the Query Optimizer to use the value 'Seattle' for local variable @city_name and to use statistical data to determine the value for the local variable @postal_code when optimizing the query. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

DECLARE @city_name nvarchar(30);  
DECLARE @postal_code nvarchar(15);  
SET @city_name = 'Ascheim';  
SET @postal_code = 86171;  
SELECT * FROM Person.Address  
WHERE City = @city_name AND PostalCode = @postal_code  
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );  
GO  

C.C. 使用 MAXRECURSIONUsing MAXRECURSION

可以使用 MAXRECURSION 来防止不合理的递归公用表表达式进入无限循环。MAXRECURSION can be used to prevent a poorly formed recursive common table expression from entering into an infinite loop. 下面的示例特意创建了一个无限循环,然后使用 MAXRECURSION 提示将递归级别限制为两级。The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

--Creates an infinite loop  
WITH cte (CustomerID, PersonID, StoreID) AS  
(  
    SELECT CustomerID, PersonID, StoreID  
    FROM Sales.Customer  
    WHERE PersonID IS NOT NULL  
  UNION ALL  
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID  
    FROM cte   
    JOIN  Sales.Customer AS e   
        ON cte.PersonID = e.CustomerID  
)  
--Uses MAXRECURSION to limit the recursive levels to 2  
SELECT CustomerID, PersonID, StoreID  
FROM cte  
OPTION (MAXRECURSION 2);  
GO  

在更正代码错误之后,就不再需要 MAXRECURSION。After the coding error is corrected, MAXRECURSION is no longer required.

D.D. 使用 MERGE UNIONUsing MERGE UNION

以下示例使用 MERGE UNION 查询提示。The following example uses the MERGE UNION query hint. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

SELECT *  
FROM HumanResources.Employee AS e1  
UNION  
SELECT *  
FROM HumanResources.Employee AS e2  
OPTION (MERGE UNION);  
GO  

E.E. 使用 HASH GROUP 和 FASTUsing HASH GROUP and FAST

以下示例使用 HASH GROUP 和 FAST 查询提示。The following example uses the HASH GROUP and FAST query hints. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (HASH GROUP, FAST 10);  
GO    

F.F. 使用 MAXDOPUsing MAXDOP

以下示例使用 MAXDOP 查询提示。The following example uses the MAXDOP query hint. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2);    
GO

G.G. 使用 INDEXUsing INDEX

以下示例使用 INDEX 提示。The following examples use the INDEX hint. 第一个示例指定了一个索引。The first example specifies a single index. 第二个示例为单个表引用指定多个索引。The second example specifies multiple indexes for a single table reference. 在这两个示例中,由于对使用别名的表应用了 INDEX 提示,因此 TABLE HINT 子句还必须将相同的别名指定为公开的对象名称。In both examples, because you apply the INDEX hint on a table that uses an alias, the TABLE HINT clause must also specify the same alias as the exposed object name. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

EXEC sp_create_plan_guide   
    @name = N'Guide1',   
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e   
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 2;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_ManagerID)))';  
GO  
EXEC sp_create_plan_guide   
    @name = N'Guide2',   
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e  
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 2;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';  
GO    

H.H. 使用 FORCESEEKUsing FORCESEEK

下面的示例使用 FORCESEEK 表提示。The following example uses the FORCESEEK table hint. TABLE HINT 子句还必须指定与公开的对象名称相同的名称(包含两部分)。The TABLE HINT clause must also specify the same two-part name as the exposed object name. 将 INDEX 提示应用于名称包含两部分的表时指定名称。Specify the name when you apply the INDEX hint on a table that uses a two-part name. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

EXEC sp_create_plan_guide   
    @name = N'Guide3',   
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title  
              FROM HumanResources.Employee  
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID  
              WHERE HumanResources.Employee.ManagerID = 3  
              ORDER BY c.LastName, c.FirstName;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';  
GO    

I.I. 使用多个表提示Using multiple table hints

下面的示例将 INDEX 提示应用到一个表,将 FORCESEEK 提示应用到另一个表。The following example applies the INDEX hint to one table and the FORCESEEK hint to another. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

EXEC sp_create_plan_guide   
    @name = N'Guide4',   
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e   
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 3;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT (e, INDEX( IX_Employee_ManagerID))   
                       , TABLE HINT (c, FORCESEEK))';  
GO  

J.J. 使用 TABLE HINT 覆盖现有的表提示Using TABLE HINT to override an existing table hint

下面的示例展示了如何使用 TABLE HINT 提示。The following example shows how to use the TABLE HINT hint. 使用提示时,可以不指定提示替代在查询的 FROM 子句中指定的 INDEX 表提示行为。You can use the hint without specifying a hint to override the INDEX table hint behavior you specify in the FROM clause of the query. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

EXEC sp_create_plan_guide   
    @name = N'Guide5',   
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))  
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 3;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT(e))';  
GO    

K.K. 指定语义影响的表提示Specifying semantics-affecting table hints

以下示例在查询中包含了NOLOCK 和 INDEX 这两个表提示,其中前者会影响语义,后者不影响语义。The following example contains two table hints in the query: NOLOCK, which is semantic-affecting, and INDEX, which is non-semantic-affecting. 若要保留查询的语义,应在计划指南的 OPTIONS 子句中指定 NOLOCK 提示。To preserve the semantics of the query, the NOLOCK hint is specified in the OPTIONS clause of the plan guide. 与 NOLOCK 提示一起,在语句编译和优化期间指定 INDEX 和 FORCESEEK 提示,并替换查询中不影响语义的 INDEX 提示。Along with the NOLOCK hint, specify the INDEX and FORCESEEK hints and replace the non-semantic-affecting INDEX hint in the query during statement compilation and optimization. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

EXEC sp_create_plan_guide   
    @name = N'Guide6',   
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e   
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))  
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 3;',  
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT (e, INDEX(IX_Employee_ManagerID), NOLOCK, FORCESEEK))';  
GO    

下面的示例演示另一种保留查询语义并使优化器能够选择并非在表提示中指定的索引的方法。The following example shows an alternative method to preserving the semantics of the query and allowing the optimizer to choose an index other than the index specified in the table hint. 允许优化器通过在 OPTIONS 子句中指定 NOLOCK 提示来进行选择。Allow the optimizer to choose by specifying the NOLOCK hint in the OPTIONS clause. 指定提示是因为它会影响语义。You specify the hint because it's semantic-affecting. 然后,指定只有表引用而无 INDEX 提示的 TABLE HINT 关键字。Then, specify the TABLE HINT keyword with only a table reference and no INDEX hint. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

EXEC sp_create_plan_guide   
    @name = N'Guide7',   
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title  
              FROM HumanResources.Employee AS e   
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))  
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
              WHERE e.ManagerID = 2;',  
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints = N'OPTION (TABLE HINT (e, NOLOCK))';  
GO  

L.L. 使用 USE HINTUsing USE HINT

以下示例使用 RECOMPILE 和 USE HINT 查询提示。The following example uses the RECOMPILE and USE HINT query hints. 该示例使用 AdventureWorks2012AdventureWorks2012 数据库。The example uses the AdventureWorks2012AdventureWorks2012 database.

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (RECOMPILE, USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES', 'DISABLE_PARAMETER_SNIFFING')); 
GO  

另请参阅See Also

提示 (Transact-SQL) Hints (Transact-SQL)
sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
sp_control_plan_guide (Transact-SQL)sp_control_plan_guide (Transact-SQL)
跟踪标志 Trace Flags
Transact-SQL 语法约定Transact-SQL Syntax Conventions