查詢存放區提示

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

本文概述如何使用 查詢存放區套用查詢提示。 查詢存放區提示提供便於使用的方法,可在不變更應用程式程式碼的情況下塑造查詢計劃。

Azure SQL Database 和 Azure SQL 受控執行個體 中提供查詢存放區提示。 查詢存放區提示也是在 SQL Server 2022 (16.x) 中引進SQL Server的功能。

注意

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

概觀

在理想情況下,查詢最佳化工具會選取查詢的最佳執行計畫。 若未發生這種情況,開發人員或 DBA 可能會想要手動針對特定條件進行優化。 查詢提示是透過 OPTION 子句指定,可用來影響查詢執行行為。 雖然查詢提示有助於針對各種效能相關問題提供當地語系化的解決方案,但它們確實需要重寫原始查詢文字。 資料庫管理員和開發人員不一定會直接對 Transact-SQL 程式碼進行變更,以插入查詢提示。 Transact-SQL 可能會硬式編碼到應用程式,或由應用程式自動產生。 先前,開發人員可能必須依賴 計劃指南,這可能相當複雜。

如需可套用哪些查詢提示的資訊,請參閱 支援的查詢提示

使用查詢存放區提示的時機

如其名所示,這項功能會擴充並取決於查詢存放區。 查詢存放區可讓您擷取查詢、執行計畫和相關聯的執行時間統計資料。 在 SQL Server 2016 (13.x) 和 Azure SQL Database 中預設推出,查詢存放區大幅簡化整體效能調整客戶體驗。

查詢存放區提示的工作流程。

首先會執行查詢,然後由查詢存放區擷取。 然後 DBA 會在查詢上建立查詢存放區提示。 之後,會使用查詢存放區提示來執行查詢。

查詢存放區提示可協助解決查詢層級效能問題的範例:

  • 每次執行查詢時重新編譯查詢。
  • 限制大量插入作業的記憶體授與大小。
  • 更新統計資料時,限制平行處理原則的最大程度。
  • 使用雜湊聯結,而不是巢狀迴圈聯結。
  • 針對特定查詢使用 相容性層級 110,同時將其他專案保留在相容性層級 150 的資料庫中。
  • 停用 SELECT TOP 查詢的資料列目標優化。

若要使用查詢存放區提示:

  1. 識別您想要修改之查詢語句的查詢存放區 query_id 。 您可以透過各種方式執行這項操作:1.1。 查詢查詢存放區目錄檢視。 1.2. 使用SQL Server Management Studio內建查詢存放區報表。 1.3. 針對 Azure SQL 資料庫使用Azure 入口網站查詢效能深入解析。
  2. 使用 sys.sp_query_store_set_hints 您想要套用至查詢的 query_id 和 查詢提示字串來執行 。 此字串可以包含一或多個查詢提示。 如需完整資訊,請參閱 sys.sp_query_store_set_hints

建立之後,會保存查詢存放區提示,並存留重新開機和容錯移轉。 查詢存放區提示會覆寫硬式編碼的語句層級提示和現有的計劃指南提示。

如果查詢提示與查詢優化可能相衝突,則提示不會封鎖查詢執行,而且不會套用提示。 在提示會導致查詢失敗的情況下,系統會忽略提示,並在 sys.query_store_query_hints中檢視最新的失敗詳細資料。

觀看這段影片以取得查詢存放區提示的概觀:

查詢存放區提示系統預存程式

若要建立或更新提示,請使用 sys.sp_query_store_set_hints。 提示會以有效的字串格式 N'OPTION (...) '指定。

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

注意

如需支援的提示完整清單,請參閱 sys.sp_query_store_set_hints

若要移除與 query_id 相關聯的提示,請使用 sys.sp_query_store_clear_hints

執行計畫 XML 屬性

套用提示時,下列結果集會出現在 StmtSimpleXML 格式的執行計畫元素中:

Attribute 說明
QueryStoreStatementHintText 套用至查詢的實際查詢存放區) 提示 (
QueryStoreStatementHintId 查詢提示的唯一識別碼
QueryStoreStatementHintSource 查詢存放區提示來源 (例如:「User」)

注意

這些 XML 元素可透過 Transact-SQL 命令 SET STATISTICS XMLSET SHOWPLAN XML的輸出取得。

查詢存放區提示和功能互通性

  • 查詢存放區提示會覆寫其他硬式編碼的語句層級提示和計劃指南。
  • 查詢一律會在任何相反的查詢存放區提示執行,否則將忽略錯誤。
  • 如果查詢存放區提示衝突,SQL Server將不會封鎖查詢執行,而且不會套用查詢存放區提示。
  • 簡單參數化 - 對於符合簡單參數化資格的語句,不支援查詢存放區提示。
  • 強制參數化 - RECOMPILE 提示與資料庫層級的強制參數化設定不相容。 如果資料庫已強制參數化集,且 RECOMPILE 提示是查詢查詢存放區中所設定提示字串的一部分,SQL Server將會忽略 RECOMPILE 提示,並在套用時套用任何其他提示。
    • 此外,SQL Server將會發出警告 (錯誤碼 12461) 指出已忽略 RECOMPILE 提示。
    • 如需強制參數化使用案例考慮的詳細資訊,請參閱 使用強制參數化的指導方針
  • 手動建立查詢存放區提示可免除清除。 擷取原則的自動保留不會清除提示和查詢查詢存放區。
    • 使用者可以手動移除查詢,這也會移除相關聯的查詢存放區提示。
    • 查詢存放區CE 意見反應自動產生的提示,受限於擷取原則的自動保留來清除。
    • DOP 意見反應記憶體授與意見反應圖形查詢行為,而不需使用查詢存放區提示。 當自動保留擷取原則來清除查詢時,也會清除 DOP 意見反應和記憶體授與意見反應資料。
    • 您可以手動建立 CE 意見反應實作的相同查詢存放區提示,然後使用提示的查詢將不再受到擷取原則自動保留的清除。

查詢存放區提示和可用性群組

如需詳細資訊,請參閱次要複本查詢存放區

  • 在 SQL Server 2022 (16.x) 之前,查詢存放區提示可以套用至可用性群組的主要複本。
  • 從 SQL Server 2022 (16.x) 開始,啟用次要複本的查詢存放區時,查詢存放區提示對於可用性群組中的次要複本也是複本感知的。
  • 當您已啟用次要複本查詢存放區時,可以將查詢存放區提示新增至特定複本或複本集。 在sys.sp_query_store_set_query_hints中,這是由 @query_hint_scope 參數所設定,這是在 SQL Server 2022 (16.x) 中引進的。
  • 查詢 sys.query_store_replicas來尋找可用的複本集。
  • 尋找在具有 sys.query_store_plan_forcing_locations的次要複本上強制的計畫。

查詢存放區提示最佳做法

  • 在評估查詢是否有潛在的新查詢存放區提示之前,請先完成索引和統計資料維護。
  • 使用查詢存放區提示之前,請先在最新的相容性層級上測試您的應用程式資料庫。
    • 例如,在 SQL Server 2022 (16.x) (相容性層級 160) 中引進了參數敏感性計畫 (PSP) 優化,其會利用每個查詢的多個作用中計畫來解決非統一的資料散發。 如果您的環境無法使用最新的相容性層級,可以使用 RECOMPILE 提示查詢存放區提示在任何支援的相容性層級上。
  • 查詢存放區提示會覆寫SQL Server查詢計劃行為。 建議只有在需要解決效能相關問題時,才利用查詢存放區提示。
  • 建議您在每次資料散發變更和資料庫移轉專案期間,重新評估查詢存放區提示、語句層級提示、計劃指南和查詢存放區強制計畫。 資料散發的變更可能會導致查詢存放區提示產生次佳的執行計畫。

範例

A. 查詢存放區提示示範

下列Azure SQL資料庫中的查詢存放區提示逐步解說會透過 BACPAC 檔案 (.bacpac) 使用匯入的資料庫。 瞭解如何將新資料庫匯入至 Azure SQL 資料庫伺服器,請參閱快速入門:將 BACPAC 檔案匯入資料庫

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc 
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
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;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId], 
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
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

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
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;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

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;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
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;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. 識別查詢存放區中的查詢

下列範例會查詢 sys.query_store_query_textsys.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。

識別之後,請套用提示,以在 設定的記憶體限制 query_id 百分比中強制執行記憶體授與大小上限:

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

您也可以使用下列語法來套用查詢提示,例如強制 舊版基數估算器的選項:

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

您可以使用逗號分隔清單來套用多個查詢提示:

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

檢閱 39 的 query_id 查詢存放區提示:

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,從 query_id 39 移除提示。

EXEC sys.sp_query_store_clear_hints @query_id = 39;

另請參閱

下一步