查詢存放區（Query Store）提示

發行項
03/05/2025

適用於： SQL Server 2022 （16.x）Azure SQL 資料庫 Azure SQL 受控執行個體 Microsoft Fabric 中的 SQL 資料庫

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

查詢存放區提示適用於 Azure SQL Database 和 Azure SQL 受控實例。查詢資料存放區提示也是 SQL Server 在 SQL Server 2022 (16.x) 引進的功能。

如需設定及管理查詢資料存放區的詳細資訊，請參閱使用查詢資料存放區監視效能。
如需使用查詢存放區探索可採取動作的資訊與調整效能的詳細資訊，請參閱使用查詢存放區調整效能。
如需操作 Azure SQL Database 中查詢存放區的相關資訊，請參閱操作 Azure SQL Database 中的查詢存放區。

警告

由於 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 查詢的資料列目標優化功能。

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

針對想要修改的查詢陳述式找到查詢資料存放區 query_id。您可透過各種方式執行：
- 對查詢存放區目錄檢視進行查詢。
- 使用 SQL Server Management Studio 內建查詢存放區報表。
- 使用 Azure 入口網站的 Azure SQL Database 查詢效能深入解析。
使用此 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 屬性

當套用提示時，下列結果集會出現在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 找出強加在次要複本上的計畫。

查詢存放區提示最佳做法

在評估潛在的新查詢儲存提示之前，請先完成索引和統計資料的維護工作。
使用查詢資料存放區提示之前，請先在最新相容性層級測試您的應用程式資料庫。
- 例如，參數敏感性計畫（Parameter Sensitive Plan, 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;

最後，使用 query_id，從 39 移除提示。

EXEC sys.sp_query_store_clear_hints @query_id = 39;

共用方式為

查詢存放區（Query Store）提示

概觀

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

查詢資料存放區提示系統預存程序

執行計畫 XML 屬性

查詢存放庫提示與功能互通性

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

查詢存放區提示最佳做法

範例

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

B. 在查詢存放區識別一個查詢

意見反應

其他資源

共用方式為

查詢存放區（Query Store）提示

概觀

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

查詢資料存放區提示系統預存程序

執行計畫 XML 屬性

查詢存放庫提示與功能互通性

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

查詢存放區提示最佳做法

範例

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

B. 在查詢存放區識別一個查詢

相關內容

意見反應

其他資源