適用於：
SQL Server 2022 （16.x） 及後版本 Azure SQL Database AzureSQL Managed InstanceMicrosoft Fabric 中的 SQL 資料庫
本文概述如何使用查詢資料存放區來套用查詢提示。 查詢存放區提示在使用上更為便利，不需要變更應用程式程式碼即可讓查詢計劃成形。
- 如需使用查詢存放區設定和管理的詳細資訊，請參閱 使用查詢存放區監視效能。
- 如需使用查詢存放區探索可採取動作的資訊和調整效能的更多資訊，請參閱使用查詢存放區調整效能。
- 如需操作 Azure SQL Database 中查詢存放區的相關資訊，請參閱 操作 Azure SQL Database 中的查詢存放區。
Caution
由於 SQL Server 查詢最佳化工具通常會選擇最好的查詢執行計畫，因此，建議資深開發人員與資料庫管理員只在必要情況使用提示。 如需詳細資訊，請參閱 查詢提示。
觀看這段影片，了解查詢存放區提示的概況：
Overview
在理想情況下，查詢最佳化工具會針對查詢選取最佳執行計畫。
如果未選取最佳方案，開發人員或資料庫管理員 （DBA） 可能會想要手動針對特定條件進行優化。 查詢提示係經 OPTION 子句指定，可用來影響查詢執行行為。 儘管查詢提示可協助針對各種效能相關問題提供當地語系化的解決方案，但需要重寫原始查詢文字。 資料庫管理員和開發人員不一定能夠直接變更 Transact-SQL 程式代碼以新增查詢提示。 Transact-SQL 可能會被硬編碼到應用程式中，或由應用程式自動產生。 過去，開發人員可能必須依賴計畫指南，但該指南內容可能相當複雜。
查詢存放區提示可讓您將查詢提示插入查詢，而不需要直接修改查詢 Transact-SQL 文字，即可解決此問題。 如需可套用哪些查詢提示的詳細資訊，請參閱支援的查詢提示。
使用查詢資料存放區提示的時機
如名稱所示，這項功能會擴充，並視查詢資料存放區而定。 查詢資料存放區能夠擷取查詢、執行計畫，以及相關執行時間統計資料。 查詢存放區大幅簡化了整體效能調整的使用者體驗。 SQL Server 2016（13.x）首次引入查詢商店，現在在 SQL Server 2022（16.x）、Azure SQL Managed Instance、Azure SQL Database 以及 Microsoft Fabric 中的 SQL 資料庫中預設啟用了查詢商店。
首先執行查詢，然後由查詢儲存區（Query Store）擷取。 接著，DBA 會在查詢上建立查詢資料存放區提示。 之後，系統會使用查詢資料存放區提示來執行查詢。
查詢資料存放區提示可能有助解決查詢層級的效能問題，例如：
- 每次執行查詢時重新編譯查詢。
- 限制大量插入作業的記憶體授與大小。
- 更新統計資料時，限制平行處理原則的最大程度。
- 使用雜湊聯結，而不是巢狀迴圈聯結。
- 針對特定查詢使用相容性等級 110，同時將資料庫的其他相容性等級保持在 150。
- 停用
SELECT TOP查詢的列目標優化功能。
如要使用查詢資料存放區提示：
針對想要修改的查詢陳述式找到查詢資料存放區
query_id。 您可以透過多種方式執行此操作：- 查詢查詢存放區目錄檢視 (Transact-SQL)。
- 使用 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中檢視最新的失敗詳細數據。
使用查詢存放區提示之前
在開始使用查詢存放區提示之前，請考慮下列事項。
- 在評估查詢是否有潛在的新查詢存放區提示之前，請先完成統計數據維護和索引維護（如有必要）。 統計數據維護以及在較小程度上的索引維護，可能會解決需要查詢提示的問題。
- 使用查詢存放區提示之前，請先在最新的 相容性層級 上測試您的應用程序資料庫，以查看這是否解決了需要查詢提示的問題。
- 例如，在相容性層級 160 下的 SQL Server 2022 （16.x） 中引進了參數敏感性計劃 （PSP） 優化。 它會使用每個查詢的多個活動計劃來解決非均勻數據分佈。 如果您的環境無法使用最新的相容性層級，則可以在任何支援的相容性層級上使用包含
RECOMPILE提示的查詢存放區提示。
- 例如，在相容性層級 160 下的 SQL Server 2022 （16.x） 中引進了參數敏感性計劃 （PSP） 優化。 它會使用每個查詢的多個活動計劃來解決非均勻數據分佈。 如果您的環境無法使用最新的相容性層級，則可以在任何支援的相容性層級上使用包含
- 查詢儲存區提示會覆蓋 Database Engine 的預設查詢計劃行為。 僅在必須解決效能相關問題時，才應該使用查詢存放區提示。
- 您應該在數據量和分佈變更或進行資料庫遷移專案時，重新評估查詢存放區提示、語句層級提示、計劃指南，以及查詢存放區的強制計劃。 數據量和分佈的變更可能會導致查詢存放區提示產生次優的執行計劃。
查詢資料存放區提示系統預存程序
如要建立或更新提示，請使用 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。
Tip
您可能需要為符合查詢哈希的所有 query_id 值設定或清除提示。
dbo.sp_query_store_modify_hints_by_query_hash 是一個預存程式範例，會在迴圈中呼叫 sys.sp_query_store_set_hints 或 sys.sp_query_store_clear_hints 系統預存程式以完成這項作業。
執行計畫 XML 屬性
套用提示時，下列結果集會出現在 StmtSimpleXML 格式的執行計劃元素中：
| Attribute | Description |
|---|---|
QueryStoreStatementHintText |
套用至查詢的實際查詢存放區提示 |
QueryStoreStatementHintId |
查詢提示的唯一識別碼 |
QueryStoreStatementHintSource |
查詢存放區提示的來源 （例如， User） |
Note
這些 XML 元素可透過 SET STATISTICS XML 和 SET SHOWPLAN_XML Transact-SQL 命令的輸出來取得。
查詢存放庫提示與功能互通性
- 查詢存放區提示會覆寫其他硬式編碼陳述式層級提示與計畫指南。
- 除了
ABORT_QUERY_EXECUTION提示之外，使用查詢存放區提示的查詢一律會執行。 忽略反查詢存放區提示，否則會導致錯誤。 - 如果查詢存放區提示相矛盾，Database Engine 不會封鎖查詢執行，而且不會套用查詢存放區提示。
- 查詢存放區提示語不適用於符合簡單參數化資格的指令。
- 提示
RECOMPILE與資料庫層級設定的強制參數化不相容。 如果資料庫已設定強制參數化，而且RECOMPILE提示是查詢存放區提示的一部分，Database Engine 將會忽略RECOMPILE提示，並在有指定其他提示時套用它們。- Database Engine 發出警告（錯誤碼 12461），指出
RECOMPILE已忽略提示。 - 如需強制參數化使用案例考慮的詳細資訊，請參閱 使用強制參數化的指導方針。
- Database Engine 發出警告（錯誤碼 12461），指出
- 手動建立的查詢存放區提示不會受到查詢存放區清除過程的影響。 自動保留擷取原則不會清除提示和查詢。
Query Store 提示和次要副本
除非啟用次要複本的查詢存放區，否則查詢存放區提示對次要複本沒有任何作用。 如需詳細資訊，請參閱 可讀次要副本的查詢存放庫。
- 在 SQL Server 2022 （16.x） 和舊版中，查詢存放區提示只能在主要複本上套用。
- 在 SQL Server 2025（17.x）及更新版本中，當啟用次要副本的查詢儲存時，查詢儲存提示可以套用到可用性群組中的次要副本上。 欲獲得完整的平台支援，請參閱 查詢商店以取得可讀的次要檔案。
在次級副本上支援查詢儲存的地方：
- 當您啟用次要複本的查詢存放區時，您可以新增查詢存放區提示，使其僅在特定複本群組上生效。 若要這樣做，請在呼叫
@replica_group_id時使用 參數。 相反地，可以使用 sys.sp_query_store_clear_query_hints 從特定的副本群組中移除 Query Store 提示。 - 藉由查詢 sys.query_store_replicas來尋找可用的複本群組。
- 使用 sys.query_store_plan_forcing_locations 找出強加在次要複本上的計畫。
Examples
A. 查詢資料存放區提示示範
下列 Azure SQL Database 中的查詢存放區提示逐步解說會透過 BACPAC 檔案 （.bacpac） 使用匯入的資料庫。 瞭解如何將新的資料庫匯入 Azure SQL Database 伺服器，請參閱 快速入門：將 bacpac 檔案匯入 Azure SQL Database 或 Azure SQL 受控實例中的資料庫。
-- ************************************************************************ --
-- 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;
EXEC sys.sp_query_store_clear_hints @query_id = 39;