分享方式：

查詢存放區提示

  • 文章

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

本文概述如何套用查詢提示來使用查詢資料存放區。 查詢存放區提示在使用上更為便利，不需要變更應用程式程式碼即可讓查詢計劃成形。

查詢資料存放區提示可用於 Azure SQL Database 與 Azure SQL 受控執行個體。 查詢資料存放區提示也是 SQL Server 在 SQL Server 2022 (16.x) 引進的功能。

警告

由於 SQL Server 查詢最佳化工具通常會選擇最好的查詢執行計畫，因此，建議資深開發人員與資料庫管理員只在必要情況使用提示。 如需詳細資訊，請參閱查詢提示。

觀看這段影片，概要說明查詢資料存放區提示：

概觀

在理想情況下，查詢最佳化工具會針對查詢選取最佳執行計畫。

如果未選取最佳計畫，開發人員或 DBA 可能想要手動針對特定條件進行最佳化。 查詢提示係經 OPTION 子句指定，可用來影響查詢執行行為。 儘管查詢提示可協助針對各種效能相關問題提供當地語系化的解決方案，但需要重寫原始查詢文字。 資料庫管理員與開發人員不一定能直接變更 Transact-SQL 程式碼，以插入查詢提示。 Transact-SQL 可能會以硬式編碼成為應用程式，或由應用程式自動產生。 過去，開發人員可能必須依賴計畫指南，但該指南內容可能相當複雜。

如需可套用哪些查詢提示的詳細資訊，請參閱支援的查詢提示。

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

如名稱所示，這項功能會擴充，並視查詢資料存放區而定。 查詢資料存放區能夠擷取查詢、執行計畫，以及相關執行時間統計資料。 查詢存放區可大幅簡化整體效能微調的客戶體驗。 SQL Server 2016 (13.x) 首次引進了查詢存放區，而現在則預設會在 SQL Server 2022 (16.x)、Azure SQL 受控執行個體和 Azure SQL 資料庫中啟用。

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

查詢資料存放區提示可能有助解決查詢層級的效能問題，例如：

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

如要使用查詢資料存放區提示：

  1. 針對想要修改的查詢陳述式找到查詢資料存放區 query_id。 您可透過各種方式執行：
    • 從查詢資料存放區目錄檢視中進行查詢。
    • 使用 SQL Server Management Studio 內建查詢資料存放區報表。
    • 使用 Azure SQL Database 的 Azure 入口網站查詢效能深入解析。
  2. 使用此 query_id 及您想要套用至查詢的查詢提示字串來執行 sys.sp_query_store_set_hints。 此字串可包含一或多個查詢提示。 如需完整資訊, 請參閱 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 屬性

套用提示時，下列結果集會以 XML 格式顯示為執行計畫的 StmtSimple 元素：

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

注意

這些 XML 元素可透過輸出 Transact-SQL 命令 SET STATISTICS XML 與 SET 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 中，這是由在 2022 SQL Server (16.x) 中引進的參數 @query_hint_scope 所設定。
  • 查詢 sys.query_store_replicas 來尋找可用複本集。
  • 以 sys.query_store_plan_forcing_locations 尋找次要複本上的強制計畫。

查詢存放區提示最佳做法

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

範例

A. 查詢資料存放區提示示範

以下逐步解說是關於在 Azure SQL Database 的查詢資料存放區提示如何使用透過 BACPAC 檔案 (.bacpac) 匯入的資料庫。 瞭解如何將新資料庫匯入 Azure SQL Database 伺服器，請參閱快速入門：將 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_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。

識別之後請套用提示，以百分比表示已設定記憶體限制的記憶體授與大小上限，並強制執行至 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''))';

請檢閱 query_id 39 的查詢資料存放區提示：

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;

相關內容