sp_query_store_set_hints （Transact-SQL）

文章
08/21/2023

適用於： SQL Server 2022 (16.x) Azure SQL Database Azure SQL 受控執行個體

建立或更新指定 query_id 的查詢存放區提示。

語法

sp_query_store_set_hints
    [ @query_id = ] query_id ,
    [ @query_hints = ] 'query_hints'
    [ , [ @query_hint_scope = ] 'replica_group_id' ]
[ ; ]

引數

[ @query_id = ] query_id

來自 sys.query_store_query 的查詢存放區 query_id 資料行。 @query_id是 Bigint 。

[ @query_hints = ] N'query_hints '

查詢選項的字元字串，開頭為 'OPTION 。 @query_hints 為 Nvarchar（max） 。如需詳細資訊，請參閱本文中支援的查詢提示。

[ @query_hint_scope = ] ' replica_group_id '

根據預設，新查詢存放區提示的範圍只有本機複本。 @query_hint_scope 是 Tinyint 。這個選擇性參數會決定啟用次要複本查詢存放區時，提示將套用至次要複本的範圍。選擇性 query_hint_scope 引數只會預設為本機複本（主要或次要複本），但您可以選擇性地指定 參考 replica_group_id sys.query_store_replicas 。

傳回值

0 （成功）或 1 （失敗）。

備註

提示會以有效的 T-SQL 字串格式 N'OPTION (..)' 指定。

如果特定 query_id 沒有任何查詢存放區提示存在，則會建立新的查詢存放區提示。
如果特定 query_id 已有查詢存放區提示存在，則所提供的最後一個值將會覆寫先前為相關聯查詢指定的值。
如果query_id 不存在，將會引發錯誤。

在提示可能會導致查詢失敗的情況下，會忽略提示，且可在 sys.query_store_query_hints 檢視最新失敗詳細資料。

若要移除與 query_id 相關聯的提示，請使用系統預存程式 sys.sp_query_store_clear_hints 。

支援的查詢提示

這些查詢提示支援為查詢存放區提示：

{ HASH | ORDER } GROUP
  | { CONCAT | HASH | MERGE } UNION
  | { LOOP | MERGE | HASH } JOIN
  | EXPAND VIEWS
  | FAST number_rows
  | FORCE ORDER
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  | KEEP PLAN
  | KEEPFIXED PLAN
  | MAX_GRANT_PERCENT = percent
  | MIN_GRANT_PERCENT = percent
  | MAXDOP number_of_processors
  | NO_PERFORMANCE_SPOOL
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN
  | USE HINT ( '<hint_name>' [ , ...n ] )

目前不支援下列查詢提示：

OPTIMIZE FOR ( @var = val)
MAXRECURSION
USE PLAN （相反地，請考慮查詢存放區的原始計畫強制功能， sp_query_store_force_plan ）。
DISABLE_DEFERRED_COMPILATION_TV
DISABLE_TSQL_SCALAR_UDF_INLINING
資料表提示（例如 FORCESEEK、READUNCOMMITTED、INDEX）

權限

需要資料庫的 ALTER 許可權。

範例

在查詢資料存放區識別查詢

下列範例會查詢sys.query_store_query_text和sys.query_store_query，以傳回 所執行查詢文字片段的query_id 。

在此範例中，我們嘗試微調的 SalesLT 查詢位於範例資料庫中：

SELECT * FROM SalesLT.Address as A
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

查詢存放區不會立即將查詢資料反映至其系統檢視。

識別查詢資料存放區系統目錄檢視的查詢：

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'%PostalCode =%'
  AND query_sql_text not like N'%query_store%';
GO

在下列範例中，資料庫中先前的 SalesLT 查詢範例會識別為 query_id 39。

套用單一提示

下列範例會將 RECOMPILE 提示套用至 query_id 39，如查詢存放區中所識別：

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

下列範例會套用提示，強制舊版基數估算器 query_id 39，在查詢存放區中識別：

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

套用多個提示

下列範例會將多個查詢提示套用至 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''))';

檢視查詢存放區提示

下列範例會傳回現有的查詢存放區提示：

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc
FROM sys.query_store_query_hints
WHERE query_id = 39;

從查詢中移除提示

使用下列範例，使用 sp_query_store_clear_hints （Transact-SQL）系統預存程式，從 query_id 39 移除提示。

EXEC sys.sp_query_store_clear_hints @query_id = 39;

分享方式：