提示 (Transact-SQL) - 查詢

適用于:yes SQL Server (所有支援的版本) 是Azure SQL資料庫是Azure SQL 受控執行個體

查詢提示會指定在查詢範圍中使用指定的提示。 查詢提示會影響陳述式中的所有運算子。 如果主要查詢涉及 UNION,只有最後一個包含 UNION 作業的查詢可以有 OPTION 子句。 查詢提示是在 OPTION 子句中指定。 如果一或多個查詢提示造成查詢最佳化工具不會產生有效的計劃,就會產生 8622 錯誤。

警告

由於SQL Server查詢最佳化工具通常會選取查詢的最佳執行計畫,因此我們建議只使用提示做為資深開發人員和資料庫管理員的最後手段。

適用範圍:

Transact-SQL 語法慣例

Syntax

<query_hint> ::=
{ { HASH | ORDER } GROUP
  | { CONCAT | HASH | MERGE } UNION
  | { LOOP | MERGE | HASH } JOIN
  | DISABLE_OPTIMIZED_PLAN_FORCING
  | EXPAND VIEWS
  | FAST <integer_value>
  | FORCE ORDER
  | { FORCE | DISABLE } EXTERNALPUSHDOWN
  | { FORCE | DISABLE } SCALEOUTEXECUTION
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  | KEEP PLAN
  | KEEPFIXED PLAN
  | MAX_GRANT_PERCENT = <numeric_value>
  | MIN_GRANT_PERCENT = <numeric_value>
  | MAXDOP <integer_value>
  | MAXRECURSION <integer_value>
  | NO_PERFORMANCE_SPOOL
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | QUERYTRACEON <integer_value>
  | RECOMPILE
  | ROBUST PLAN
  | USE HINT ( <use_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> ) ]
  | 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_value>
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

<use_hint_name> ::=
{ 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
  | 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
  | 'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'
  | 'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'
  | 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
  | 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_DEFERRED_COMPILATION_TV'
  | 'DISABLE_INTERLEAVED_EXECUTION_TVF'
  | 'DISABLE_OPTIMIZED_NESTED_LOOP'
  | 'DISABLE_OPTIMIZER_ROWGOAL'
  | 'DISABLE_PARAMETER_SNIFFING'
  | 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_TSQL_SCALAR_UDF_INLINING'
  | 'DISALLOW_BATCH_MODE'
  | 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
  | 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
  | 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
  | 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
  | 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
  | 'QUERY_PLAN_PROFILE'
}

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件

引數

{ HASH | ORDER } GROUP

指定查詢之 GROUP BY 或 DISTINCT 子句所描述的彙總應使用雜湊或排序。

{ MERGE | HASH | CONCAT } UNION

指定所有 UNION 作業都是藉由合併、雜湊或串連各個 UNION 集來執行。 如果指定了多個 UNION 提示,則查詢最佳化工具會從所指定提示中選取成本最低的策略。

{ LOOP | MERGE | HASH } JOIN

指定所有聯結作業都是由整個查詢中的 LOOP JOIN、MERGE JOIN 或 HASH JOIN 來執行。 如果您指定多個聯結提示,最佳化工具會從允許使用的聯結提示中,選取成本最低的聯結策略。

如果您針對特定資料表配對在相同查詢的 FROM 子句中指定聯結提示,則會在兩個資料表的聯結中優先採用這個聯結提示。 不過,必須仍然能夠接受這些查詢提示。 這組資料表的聯結提示可能只會限制查詢提示中所允許使用聯結方法的選取。 如需詳細資訊,請參閱 Transact-SQL) (聯結提示

DISABLE_OPTIMIZED_PLAN_FORCING

適用于:從 SQL Server 2022 (16.x) Preview) 開始SQL Server (

停用查詢的 優化計畫強制

優化計畫強制可減少重複強制查詢的編譯額外負荷。 產生查詢執行計畫之後,特定編譯步驟會儲存為重複使用作為優化重新執行腳本。 優化重新執行腳本會儲存為隱藏屬性中壓縮之 showplan XML 的部分查詢存放區 OptimizationReplay

EXPAND VIEWS

指定展開索引檢視表。 另外,指定查詢最佳化工具不會考慮使用任何索引檢視表來作為任何查詢組件的取代項目。 當檢視表定義取代查詢文字中的檢視表名稱時,便會展開這份檢視表。

這個查詢提示會虛擬地禁止直接在查詢計畫中使用索引檢視表及其索引。

注意

如果查詢的 SELECT 部分有檢視表的直接參考,則索引檢視表會維持懕縮狀態。 如果您指定 WITH (NOEXPAND) 或 WITH (NOEXPAND,INDEX < ( index_value > [ , ...n ] ) ) 。 如需查詢提示 NOEXPAND 的詳細資訊,請參閱使用 NOEXPAND

這個提示只會影響陳述式 SELECT 部分中的檢視表,其中包括 INSERT、UPDATE、MERGE 和 DELETE 陳述式中的檢視表。

FAST < integer_value >

指定查詢已優化,以便快速擷取第一< 個 > integer_value數目的資料列。 此結果為非負整數。 傳回第一< 個integer_value >個數據列數目之後,查詢會繼續執行,並產生其完整的結果集。

FORCE ORDER

指定在查詢最佳化期間,保留查詢語法所指出的聯結順序。 使用 FORCE ORDER 不會影響查詢最佳化工具所可能有的角色反轉行為。

注意

在 MERGE 陳述式中,除非指定了 WHEN SOURCE NOT MATCHED 子句,否則就會根據聯結順序,先存取來源資料表,然後再存取目標資料表。 指定 FORCE ORDER 可以保留此預設行為。

{ FORCE | DISABLE } EXTERNALPUSHDOWN

強制或停用在 Hadoop 中下推合格運算式的計算。 僅適用於使用 PolyBase 的查詢。 將不會下推到 Azure 儲存體。

{ FORCE |DISABLE } SCALEOUTEXECUTION

強制或停用在 SQL Server 2019 巨量資料叢集中使用外部資料表的 PolyBase 查詢執行。 只有使用 SQL 巨量資料叢集主要執行個體的查詢才會接受此提示。 擴增會在巨量資料叢集的計算集區中發生。

KEEP PLAN

變更臨時表 的重新編譯臨界值 ,並使其與永久資料表的重新編譯閾值相同。 所預估重新編譯臨界值是資料表因執行下列其中一個陳述式而變更預估數目的索引資料行時,即會啟動查詢的自動重新編譯:

  • UPDATE
  • 刪除
  • MERGE
  • Insert

指定 KEEP PLAN 可確保查詢不會依照資料表有多項更新的頻率來重新編譯。

KEEPFIXED PLAN

強制查詢最佳化工具不因統計資料中的變更而重新編譯查詢。 指定 KEEPFIXED PLAN 可確保只有在基礎資料表的架構變更時,或針對 sp_recompile 這些資料表執行時,查詢才會重新編譯。

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX

適用于:從 SQL Server 2012 (11.x) ) 開始SQL Server (。

防止查詢使用非叢集之記憶體最佳化的資料行存放區索引。 如果查詢同時包含禁止使用資料行存放區索引的查詢提示,以及使用資料行索引的索引提示,將會因為兩提示相互衝突而傳回錯誤。

MAX_GRANT_PERCENT = < numeric_value>

適用于:從 SQL Server 2016 (13.x) ) 和 Azure SQL Database 開始SQL Server (。

記憶體授與大小上限 (以已設定的記憶體限制百分比表示)。 查詢保證不會超過此限制。 如果 Resource Governor 設定低於此提示所指定的值,實際限制可能更低。 有效值介於 0.0 與 100.0 之間。

MIN_GRANT_PERCENT = < numeric_value>

適用于:從 SQL Server 2016 (13.x) ) 和 Azure SQL Database 開始SQL Server (。

記憶體授與大小下限 (以已設定的記憶體限制百分比) 表示。 保證查詢會取得 MAX(required memory, min grant),因為至少需要有所需的記憶體,才能啟動查詢。 有效值介於 0.0 與 100.0 之間。

MAXDOP < integer_value>

適用于:從 SQL Server 2008) 和 Azure SQL Database 開始SQL Server (。

覆寫 的平行處理原則最大程度 組態選項 sp_configure 。 也會針對指定這個選項的查詢覆寫 Resource Governor。 MAXDOP 查詢提示可以超過使用 sp_configure 設定的值。 如果 MAXDOP 超過使用 Resource Governor 設定的值,Database Engine 會使用 RESOURCE GOVERNOR MAXDOP 值,如ALTER WORKLOAD GROUP (Transact-SQL) 中所述。 當您使用 MAXDOP 查詢提示時,適用所有搭配 max degree of parallelism 組態選項使用的語意規則。 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項

警告

如果 MAXDOP 設為零,則伺服器會選擇平行處理原則的最大程度。

MAXRECURSION < integer_value>

指定此查詢所能擁有的最大遞迴數。 number 是 0 與 32767 之間的非負整數。 當指定 0 時,不會套用任何限制。 如果未指定這個選項,則伺服器的預設限制為 100。

在查詢執行期間,當到達 MAXRECURSION 限制的指定或預設數目時,查詢會結束且會傳回錯誤。

陳述式的所有效果都會因這個錯誤而回復。 如果陳述式是 SELECT 陳述式,可能會傳回部分結果,或根本不傳回任何結果。 任何傳回的部分結果都不會包括超出指定的最大遞迴層級之遞迴層級的所有資料列。

如需詳細資訊,請參閱 WITH common_table_expression (Transact-SQL)

NO_PERFORMANCE_SPOOL

適用于:從 SQL Server 2016 (13.x) ) 和 Azure SQL Database 開始SQL Server (。

防止多工緩衝處理運算子加入查詢計劃 (需有多工緩衝處理才能保證有效的更新語意的計劃除外)。 在某些情況下,多工緩衝處理運算子可能會降低效能。 例如,如果有許多並行查詢與多工緩衝處理作業一起執行,則多工緩衝處理會使用 tempdb ,而且 tempdb 可能會發生爭用。

OPTIMIZE FOR ( @variable_name { UNKNOWN | = < literal_constant > } [ , ...n ] )

指示查詢最佳化工具在查詢進行編譯和最佳化時,使用特定的區域變數值。 只有在查詢最佳化期間,才使用這個值,在查詢執行期間,不使用這個值。

@variable_name
這是查詢所用之本機變數的名稱,您可以指派這個本機變數的值來搭配使用 OPTIMIZE FOR 查詢提示。

UNKNOWN
指定查詢最佳化工具使用統計資料 (而非初始值) 來判斷查詢最佳化期間的區域變數值。

<literal_constant>
這是要指派 @variable_name 以搭配 OPTIMIZE FOR 查詢提示使用的常值。 <> literal_constant只會在查詢優化期間使用,而不是查詢執行期間@variable_name的值。 <> literal_constant可以是任何可表示為常值常數的SQL Server系統資料類型。 literal_constant > 的 <資料類型必須隱含轉換成查詢中@variable_name參考的資料類型。

OPTIMIZE FOR 可以抵制最佳化工具的預設參數偵測行為。 當您建立計畫指南時,也請使用 OPTIMIZE FOR。 如需詳細資訊,請參閱重新編譯預存程序

OPTIMIZE FOR UNKNOWN

指示查詢最佳化工具在所有資料行值上使用述詞的平均選擇性,而非在查詢已編譯並最佳化後使用執行階段參數值。

如果您在相同的查詢提示中使用 OPTIMIZE FOR @variable_name = <literal_constant>OPTIMIZE FOR UNKNOWN ,查詢最佳化工具將會使用針對特定值指定的 literal_constant 。 查詢最佳化工具會將 UNKNOWN 用於其餘的變數值。 只有在查詢最佳化期間才使用這些值,查詢執行期間則不使用這些值。

PARAMETERIZATION { SIMPLE | FORCED }

指定SQL Server查詢最佳化工具在編譯時套用至查詢的參數化規則。

重要

您只能在計劃指南內指定 PARAMETERIZATION 查詢提示,以覆寫 PARAMETERIZATION 資料庫 SET 選項目前的設定。 您不能在查詢中直接指定它。

如需詳細資訊,請參閱使用計劃指南指定查詢參數化行為

SIMPLE 指示查詢最佳化工具嘗試使用簡單參數化。 FORCED 指示查詢最佳化工具嘗試使用強制參數化。 如需詳細資訊,請參閱查詢處理架構指南中的強制參數化查詢處理架構指南中的簡單參數化

QUERYTRACEON < integer_value>

此選項可讓您只在單一查詢編譯期間啟用計畫影響追蹤旗標。 如同其他查詢層級的選項,您可以將此選項與計畫指南一起使用,以便比對任何工作階段所執行查詢的文字,並在編譯此查詢時,自動套用影響計畫的追蹤旗標。 只針對查詢最佳化工具追蹤旗標支援 QUERYTRACEON 選項。 如需詳細資訊,請參閱追蹤旗標

如果使用不支援的追蹤旗標編號,則使用此選項不會傳回任何錯誤或警告。 如果指定的追蹤旗標不是影響查詢執行計畫的旗標,則會以無訊息方式忽略此選項。

若要在查詢中使用一個以上的追蹤旗標,請為每個不同的追蹤旗標編號指定一個 QUERYTRACEON 提示。

RECOMPILE

指示 SQL Server Database Engine 產生查詢的新暫存計畫,並在查詢完成執行之後立即捨棄該計畫。 在沒有 RECOMPILE 提示的情況下執行相同查詢時,產生的查詢計畫不會取代快取中儲存的計畫。 若未指定 RECOMPILE,Database Engine 會快取查詢計劃並重複使用它們。 在編譯查詢計畫時,RECOMPILE 查詢提示會在查詢中使用任何區域變數的目前值。 如果查詢是在預存程序內,則將目前值傳遞給任何參數。

RECOMPILE 是建立預存程序的有用替代方案。 當不必重新編譯整個預存程序,而只需要重新編譯預存程序內的部分查詢時,RECOMPILE 會使用 WITH RECOMPILE 子句。 如需詳細資訊,請參閱重新編譯預存程序。 另外,當您建立計畫指南時,RECOMPILE 也非常有用。

ROBUST PLAN

強制查詢最佳化工具嘗試一項適用於最大潛在資料列大小的計劃,可能會犧牲效能。 當處理查詢時,中繼資料表和運算子可能需要儲存和處理比任何輸入資料列還寬的資料列。 這些資料列的寬度,有時會使特定運算子無法處理資料列。 如果資料列寬,Database Engine 會在查詢執行期間產生錯誤。 您可以使用 ROBUST PLAN 來指示查詢最佳化工具,不考慮任何可能遇到這個問題的查詢計劃。

如果無法執行這類計劃,則查詢最佳化工具會傳回錯誤,而不是將錯誤偵測延遲到查詢執行時。 資料列可能包含可變長度的資料行;資料庫引擎允許定義資料列,其大小上限超過 Database Engine 處理它們的能力。 一般而言,儘管可能的大小上限,應用程式仍會儲存在 Database Engine 可以處理的限制內具有實際大小的資料列。 如果 Database Engine 遇到太長的資料列,則會傳回執行錯誤。

使用 HINT ( 'hint_name' )

適用于:從 SQL Server 2016 (13.x) SP1) 和 Azure SQL Database 開始SQL Server (。

為查詢處理器提供一或多個其他提示。 其他提示由單引號內的提示名稱所指定。

支援下列提示名稱:

  • 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
    導致SQL Server使用簡單內含專案假設來產生查詢計劃,而不是聯結的預設基底內含專案假設,在 SQL Server 2014 (12.x) 或更新版本下,查詢最佳化工具基數估計模型。 這個提示名稱與追蹤旗標 9476 相同。

  • 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
    在估計篩選準則的 AND 述詞以考慮完整相互關聯時,會導致SQL Server使用最小選擇性來產生計畫。 當搭配 SQL Server 2012 (11.x) 和舊版的基數估計模型使用時,這個提示名稱相當於追蹤旗標 4137,而且當追蹤旗標 9471 搭配 SQL Server 2014 (12.x) 或更高版本的基數估計模型使用時,會有類似的效果。

  • 'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'
    在估計篩選準則的 AND 述詞以考慮完整獨立性時,會導致SQL Server產生使用最大選擇性的計畫。 這個提示名稱是 2012 SQL Server 2012 (11.x) 和舊版基數估計模型的預設行為,相當於搭配 SQL Server 2014 (12.x) 或更高版本的基數估計模型使用追蹤旗標9472。
    適用于:Azure SQL資料庫

  • 'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'
    在估計篩選準則的 AND 述詞以考慮部分相互關聯時,會導致SQL Server產生最不具選擇性的計畫。 此提示名稱是 SQL Server 2014 (12.x) 或更高版本基數估計模型的預設行為。
    適用于:Azure SQL資料庫

  • 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
    停用批次模式自適性聯結。 如需詳細資訊,請參閱批次模式自適性聯結
    適用于:SQL Server (從 2017 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始

  • 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
    停用批次模式記憶體授與意見反應。 如需詳細資訊,請參閱批次模式記憶體授與意見反應
    適用于:SQL Server (從 2017 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始

  • 'DISABLE_DEFERRED_COMPILATION_TV'
    停用資料表變數延後編譯。 如需詳細資訊,請參閱資料表變數延遲編譯.
    適用于:SQL Server (從 2019 SQL Server 2019 (15.x) ) 和 Azure SQL Database 開始

  • 'DISABLE_INTERLEAVED_EXECUTION_TVF'
    停用交錯執行多重陳述式資料表值函式。 如需詳細資訊,請參閱交錯執行多重陳述式資料表值函式
    適用于:SQL Server (從 2017 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始

  • 'DISABLE_OPTIMIZED_NESTED_LOOP'
    指示查詢處理器在產生查詢計劃時,不使用排序作業 (批次排序) 以取得最佳化巢狀迴圈聯結。 這個提示名稱與追蹤旗標 2340 相同。

  • 'DISABLE_OPTIMIZER_ROWGOAL'
    導致 SQL Server 產生的計畫不使用資料列目標調整來處理包含下列關鍵字的查詢:

    • 頂端
    • OPTION (FAST N)
    • IN
    • EXISTS

    這個提示名稱與追蹤旗標 4138 相同。

  • 'DISABLE_PARAMETER_SNIFFING'
    指示查詢最佳化工具在編譯有一或多個參數的查詢時,使用平均資料分佈。 這個指令會讓查詢計畫與查詢在編譯時一開始使用的參數值無關。 這個提示名稱與追蹤旗標 4136 或資料庫範圍設定PARAMETER_SNIFFING = OFF 設定相同。

  • 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
    停用資料列模式記憶體授與意見反應。 如需詳細資訊,請參閱資料列模式記憶體授與意見反應
    適用于:SQL Server (從 2019 SQL Server 2019 (15.x) ) 和 Azure SQL Database 開始

  • 'DISABLE_TSQL_SCALAR_UDF_INLINING'
    停用純量 UDF 內嵌。 如需詳細資訊,請參閱純量 UDF 內嵌
    適用于:SQL Server (從 2019 SQL Server 2019 (15.x) ) 和 Azure SQL Database 開始

  • 'DISALLOW_BATCH_MODE'
    停用批次模式執行。 如需詳細資訊,請參閱執行模式
    適用于:SQL Server (從 2019 SQL Server 2019 (15.x) ) 和 Azure SQL Database 開始

  • 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
    為需要基數估計的任何開頭索引資料行,啟用自動產生的快速統計資料 (長條圖修正)。 在查詢編譯時會調整用來預估基數的長條圖,以計算此資料行的實際最大值或最小值。 這個提示名稱與追蹤旗標 4139 相同。

  • 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
    啟用查詢最佳化工具 Hotfix (在 SQL Server 累積更新和 Service Pack 中發佈的變更)。 這個提示名稱與追蹤旗標 4199 或資料庫範圍設定QUERY_OPTIMIZER_HOTFIXES = ON 設定相同。

  • 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
    強制查詢最佳化工具使用對應至目前資料庫相容性層級的基數估計模型。 使用這個提示來覆寫資料庫範圍設定LEGACY_CARDINALITY_ESTIMATION = ON 設定或追蹤旗標 9481。

  • 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
    強制查詢最佳化工具使用 SQL Server 2012 (11.x) 和舊版的基數估計模型。 這個提示名稱與追蹤旗標 9481 或資料庫範圍設定LEGACY_CARDINALITY_ESTIMATION = ON 設定相同。

  • 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
    強制執行查詢層級的查詢最佳化工具行為。 此行為如同查詢是使用資料庫相容性層級 n 所編譯,其中 n 是支援的資料庫相容性層級 (例如 100、130 等)。 請參閱 sys.dm_exec_valid_use_hints 以取得目前支援之 n 值的清單。
    適用于:從 2017 SQL Server 2017 (14.x) CU10) 和 Azure SQL Database 開始SQL Server (

    注意

    QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 提示不會覆寫預設值或舊版基數估計設定,若它是透過資料庫範圍設定所強制,則為追蹤旗標或另一個查詢提示,例如 QUERYTRACEON。
    此提示只會影響查詢最佳化工具的行為。 這不會影響可能相依于資料庫相容性層級之SQL Server的其他功能,例如特定資料庫功能的可用性。
    若要深入了解此提示,請參閱 Developer's Choice:Hinting Query Execution model (開發人員精選:提示查詢執行模型)。

  • 'QUERY_PLAN_PROFILE'
    為查詢啟用輕量分析。 當包含這個新提示的查詢完成時,便會發出一個新的擴充事件 (query_plan_profile)。 此擴充事件會公開執行統計資料和與 query_post_execution_showplan 擴充事件相似的執行計畫 XML,但僅限包含新提示的查詢。
    適用于:SQL Server (從 SQL Server 2016 (13.x) SP2 CU3 開始,SQL Server 2017 (14.x) CU11) 。

    注意

    若您啟用收集 query_post_execution_showplan 擴充事件,這會將標準分析基礎結構新增至每個正在伺服器上執行的查詢,並因此影響整體伺服器效能。
    若您啟用收集 query_thread_profile 擴充事件以改為使用輕量分析基礎結構,這會大幅減少效能額外負荷,但仍然會影響整體伺服器效能。
    若您啟用 query_plan_profile 擴充事件,這只會為使用 QUERY_PLAN_PROFILE 執行的查詢啟用輕量分析基礎結構,因此不會影響伺服器上的其他工作負載。 使用提示分析特定查詢,而不影響伺服器工作負載的其他部分。 若要深入了解輕量型分析,請參閱查詢分析基礎結構

您可以使用動態管理檢視 sys.dm_exec_valid_use_hints,來查詢所有支援的 USE HINT 名稱清單。

秘訣

提示名稱不區分大小寫。

重要

一些 USE HINT 提示可能會與在全域或工作階段層級啟用的追蹤旗標發生衝突,或與資料庫範圍設定的設定發生衝突。 在此情況下,查詢層級提示 (USE HINT) 一律優先。 如果 USE HINT 與另一個查詢提示發生衝突,或查詢層級啟用的追蹤旗標 (,例如 QUERYTRACEON) ,則嘗試執行查詢時,SQL Server會產生錯誤。

使用方案N'xml_plan <>'

強制查詢最佳化工具針對 '< xml_plan >' 所指定的查詢使用現有的查詢計劃。 USE PLAN 無法搭配 INSERT、UPDATE、MERGE 或 DELETE 陳述式一起指定。

這項功能強制產生的執行計畫將會與強制計畫相同或類似。 由於產生的計畫可能與 USE PLAN 所指定的計畫不相同,因此計畫效能可能會有所不同。 在罕見的情況下,效能差異可能是顯著且負面的;在此情況下,系統管理員必須移除強制計畫。

TABLE HINT (< exposed_object_name > [ , < table_hint > [ [, ]...n ] )

將指定的資料表提示套用至對應至 exposed_object_name的資料表或檢視表。 我們建議您只在 計劃指南的內容中,才將資料表提示當做查詢提示使用。

< exposed_object_name >可以是下列其中一個參考:

  • 當別名用於查詢 之 FROM 子句中的資料表或檢視時, exposed_object_name 是別名。

  • 不使用別名時, exposed_object_name 是 FROM 子句中所參考資料表或檢視表的完全相符專案。 例如,如果使用兩部分名稱參考資料表或檢視表, exposed_object_name 是相同的兩部分名稱。

當您指定 exposed_object_name 而不指定資料表提示時,系統會忽略您在查詢中指定做為物件資料表提示一部分的任何索引。 然後,查詢最佳化工具會決定索引使用方式。 當您無法修改原始的查詢時,就可以使用這項技巧來排除 INDEX 資料表提示的影響。 請參閱<範例 J>。

<> table_hint ::= {
NOEXPAND [ , INDEX ( < index_value > [ ,...n ] ) |INDEX = ( < index_value > ) ] |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_value > |TABLOCK |TABLOCKX |UPDLOCK |XLOCK }
這是要套用至以查詢提示形式對應到 exposed_object_name 之資料表或檢視的資料表提示。 如需這些提示的描述,請參閱 資料表提示 (Transact-SQL)

除非查詢已有指定資料表提示的 WITH 子句,否則不可使用 INDEX、FORCESCAN 和 FORCESEEK 以外的資料表提示做為查詢提示。 如需詳細資訊,請參閱備註一節。

警告

相較於未搭配參數指定 FORCESEEK,搭配參數指定 FORCESEEK 能夠限制更多查詢最佳化工具可考量的計畫數目。 這可能會導致在許多狀況下發生「無法產生計畫」錯誤。 在未來的版本中,將從內部修改查詢最佳化工具,以便能夠將更多計畫納入考量。

備註

除非是在陳述式內使用 SELECT 子句,否則無法在 INSERT 陳述式中指定查詢提示。

您只能在最上層查詢中指定查詢提示,不能在子查詢中指定查詢提示。 當資料表提示指定為查詢提示時,可以在最上層查詢或子查詢中指定提示。 不過,針對 TABLE HINT 子句中exposed_object_name > 指定的 <值必須完全符合查詢或子查詢中公開的名稱。

將資料表提示指定為查詢提示

建議您只有在計劃指南的內容中,才將 INDEX、FORCESCAN 或 FORCESEEK 資料表提示作為查詢提示使用。 當您無法修改原始的查詢 (例如,因為它是協力廠商應用程式) 時,計畫指南就很有用。 在計畫指南中指定的查詢提示會先新增至查詢,再進行編譯和最佳化。 若為特定查詢,請在測試計畫指南陳述式時才使用 TABLE HINT 子句。 如果是所有其他特定的查詢,我們建議您將這些提示指定成資料表提示。

將 INDEX、FORCESCAN 和 FORCESEEK 資料表提示指定為查詢提示適用於下列物件:

  • 資料表
  • 檢視
  • 索引檢視
  • 通用資料表運算式 (此提示必須指定於結果集擴展此通用資料表運算式的 SELECT 陳述式內)
  • 動態管理檢視 (DMV)
  • 具名子查詢

您可以針對沒有任何現有資料表提示的查詢,指定 INDEX、FORCESCAN 和 FORCESEEK 資料表提示作為查詢提示。 您也可以使用它們分別取代查詢中的現有 INDEX、FORCESCAN 或 FORCESEEK 提示。

除非查詢已有指定資料表提示的 WITH 子句,否則不可使用 INDEX、FORCESCAN 和 FORCESEEK 以外的資料表提示做為查詢提示。 在此情況下,還必須指定相符的提示作為查詢提示。 請在 OPTION 子句中使用 TABLE HINT,以指定相符的提示作為查詢提示。 此規格會保留查詢的語意。 例如,如果查詢包含資料表提示 NOLOCK,則計劃指南 的 @hints 參數中的 OPTION 子句也必須包含 NOLOCK 提示。 請參閱<範例 K>。

使用查詢存放區提示指定提示

您可以使用查詢存放區提示功能,對透過查詢存放區識別的查詢強制執行提示,而不進行程式碼變更。 使用 sys.sp_query_store_set_hints 預存程式將提示套用至查詢。 請參閱範例 N。

範例

A. 使用 MERGE JOIN

下列範例指定 MERGE JOIN 執行查詢中的 JOIN 作業。 此範例使用 AdventureWorks2012 資料庫。

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. 使用 OPTIMIZE FOR

下列範例會指示查詢最佳化工具使用 'Seattle' 值做為 @city_name,然後於最佳化查詢時在適用於 @postal_code 的所有資料行值上使用述詞的平均選擇性。 此範例使用 AdventureWorks2012 資料庫。

CREATE PROCEDURE dbo.RetrievePersonAddress
@city_name NVARCHAR(30),
@postal_code NVARCHAR(15)
AS
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO

C. 使用 MAXRECURSION

您可以利用 MAXRECURSION 來防止形式不良的遞迴通用資料表運算式進入無限迴圈。 下列範例會刻意建立無限迴圈,然後利用 MAXRECURSION 提示,將遞迴層級數目限制為 2。 此範例使用 AdventureWorks2012 資料庫。

--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。

D. 使用 MERGE UNION

下列範例使用 MERGE UNION 查詢提示。 此範例使用 AdventureWorks2012 資料庫。

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

E. 使用 HASH GROUP 與 FAST

下列範例使用 HASH GROUP 和 FAST 查詢提示。 此範例使用 AdventureWorks2012 資料庫。

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. 使用 MAXDOP

下列範例使用 MAXDOP 查詢提示。 此範例使用 AdventureWorks2012 資料庫。

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. 使用 INDEX

下列範例使用 INDEX 提示。 第一個範例會指定單一索引。 第二個範例會針對單一資料表參考指定多個索引。 在這兩個範例中,由於您將 INDEX 提示套用在使用別名的資料表上,因此 TABLE HINT 子句也必須與公開物件名稱指定相同的別名。 此範例使用 AdventureWorks2012 資料庫。

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. 使用 FORCESEEK

下列範例使用 FORCESEEK 資料表提示。 TABLE HINT 子句也必須指定與公開物件名稱相同的兩段式名稱。 當您在使用兩段式名稱的資料表上套用 INDEX 提示時,請指定名稱。 此範例使用 AdventureWorks2012 資料庫。

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. 使用多個資料表提示

下列範例會將 INDEX 提示套用到某個資料表,並將 FORCESEEK 提示套用到另一個資料表。 此範例使用 AdventureWorks2012 資料庫。

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. 使用 TABLE HINT 覆寫現有的資料表提示

下列範例顯示要如何使用 TABLE HINT 提示。 您可以使用提示,而不指定提示來覆寫您在查詢 FROM 子句中指定的 INDEX 資料表提示行為。 此範例使用 AdventureWorks2012 資料庫。

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. 指定影響語意的資料表提示

下列範例在查詢中包含兩個資料表提示:影響語意的 NOLOCK,以及不會影響語意的 INDEX。 為了保留查詢的語意,會在計畫指南的 OPTIONS 子句中指定 NOLOCK 提示。 除了 NOLOCK 提示以外,在陳述式編譯和最佳化期間,也會指定 INDEX 和 FORCESEEK 提示,並用它們來取代查詢中不會影響語意的 INDEX 提示。 此範例使用 AdventureWorks2012 資料庫。

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

下列範例示範另一個方法來保留查詢的語意,並讓最佳化工具選擇使用不是資料表提示中所指定的索引。 可讓最佳化工具在 OPTIONS 子句中指定 NOLOCK 提示來進行選擇。 您會指定此提示,因為它會影響語意。 然後,指定只包含資料表參考的 TABLE HINT 關鍵字,而不指定 INDEX 提示。 此範例使用 AdventureWorks2012 資料庫。

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. 使用 USE HINT

下列範例使用 RECOMPILE 和 USE HINT 查詢提示。 此範例使用 AdventureWorks2012 資料庫。

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

M. 使用 QUERYTRACEON 提示

下列範例使用 QUERYTRACEON 查詢提示。 此範例使用 AdventureWorks2012 資料庫。 您可以使用下列查詢,針對特定查詢啟用由追蹤旗標 4199 所控制所有影響計畫的 Hotfix:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (QUERYTRACEON 4199);

您也可以使用多個追蹤旗標,如下列查詢所示:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION  (QUERYTRACEON 4199, QUERYTRACEON 4137);

N. 使用查詢存放區提示

Azure SQL 資料庫中的查詢存放區提示功能提供容易使用的方法,可在不變更應用程式程式碼的情況下成形查詢計劃。

首先,識別已在查詢存放區目錄檢視中執行的查詢,例如:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'
  AND query_sql_text not like N'%query_store%';
GO

下列範例會套用提示來強制舊版基數估算器query_id 39,查詢存放區:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

下列範例會套用提示,以在已設定記憶體限制的 PERCENT 中強制執行記憶體授與大小上限,以查詢存放區識別為 query_id 39:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

下列範例會將多個查詢提示套用至 query_id 39,包括 RECOMPILE、MAXDOP 1 和 SQL 2012 查詢最佳化工具行為:

EXEC sys.sp_query_store_set_hints @query_id= 39,
    @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

下一步