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

適用於: 是SQL Server 是Azure SQL Database 否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_rowsFAST number_rows
指定將查詢最佳化,以快速擷取第一個 number_rowsSpecifies 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
  • DeleteDELETE
  • 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) 起,到 SQL ServerSQL ServerApplies to: SQL ServerSQL Server (staring with SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL ServerSQL Server.

防止查詢使用非叢集之記憶體最佳化的資料行存放區索引。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. 查詢保證取得 MAX(所需的記憶體, 最小授與),因為至少需有所需的記憶體,才能啟動查詢。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_configuremax degree of parallelism 設定選項。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 EngineDatabase Engine 就會使用 ALTER WORKLOAD GROUP (Transact-SQL) 中所描述的 Resource Governor MAXDOP 值。If MAXDOP exceeds the value configured with Resource Governor, the Database EngineDatabase 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 與 32767 之間的非負整數。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.

防止多工緩衝處理運算子加入查詢計劃 (需有多工緩衝處理才能保證有效的更新語意的計劃除外)。Prevents a spool operator from being added to query plans (except for the plans when spool is required to guarantee valid update semantics). 在某些情況下,多工緩衝處理運算子可能會降低效能。The spool operator may reduce performance in some scenarios. 例如,多工緩衝處理會使用 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.

UNKNOWNUNKNOWN
指定查詢最佳化工具使用統計資料 (而非初始值) 來判斷查詢最佳化期間的區域變數值。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_constantliteral_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_constant 用於特定值。If 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 Database EngineSQL Server Database Engine 針對查詢產生新的暫時計畫,並在查詢完成執行之後立即捨棄該計畫。Instructs the SQL Server Database EngineSQL 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 EngineDatabase Engine 會快取查詢計劃並重複使用。Without specifying RECOMPILE, the Database EngineDatabase 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 EngineDatabase Engine 會產生一則錯誤。If rows are that wide, the Database EngineDatabase 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 EngineDatabase Engine 允許資料列定義成超出 Database EngineDatabase Engine 處理能力的最大潛在大小。Rows may contain variable-length columns; the Database EngineDatabase Engine allows for rows to be defined that have a maximum potential size beyond the ability of the Database EngineDatabase Engine to process them. 一般而言,雖然有最大潛在大小,但應用程式仍會儲存實際大小在 Database EngineDatabase Engine 處理能力限制之內的資料列。Generally, despite the maximum potential size, an application stores rows that have actual sizes within the limits that the Database EngineDatabase Engine can process. 如果 Database EngineDatabase Engine 遇到太長的資料列,便會傳回執行錯誤。If the Database EngineDatabase 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 相同,而且在將追蹤旗標 9471 搭配 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 或更新版本的基數估計模型使用時,會有類似效果。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'
    啟用查詢最佳化工具 Hotfix (在 SQL Server 累積更新和 Service Pack 中發佈的變更)。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. 此擴充事件會公開執行統計資料和與 query_post_execution_showplan 擴充事件相似的執行計畫 XML,但僅限包含新提示的查詢。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'. USE PLAN 無法搭配 INSERT、UPDATE、MERGE 或 DELETE 陳述式一起使用。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).

除非查詢已有指定資料表提示的 WITH 子句,否則不可使用 INDEX、FORCESCAN 和 FORCESEEK 以外的資料表提示做為查詢提示。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

除非是在陳述式內使用 SELECT 子句,否則無法在 INSERT 陳述式中指定查詢提示。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.

除非查詢已有指定資料表提示的 WITH 子句,否則不可使用 INDEX、FORCESCAN 和 FORCESEEK 以外的資料表提示做為查詢提示。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

下列範例指示查詢最佳化工具在最佳化查詢時,將 'Seattle' 值用於區域變數 @city_name 並使用統計資料來判斷區域變數 @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 提示,將遞迴層級數目限制為 2。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. 然後,指定只包含資料表參考的 TABLE HINT 關鍵字,而不指定 INDEX 提示。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