Query Store 是什麼#

Query Store 從 SQL Server 2016 引入，是 SQL Server 與 Azure SQL Database 內建的查詢效能歷史資料庫。它做四件事：

• 保留查詢效能與等待指標（聚合）
• 保留執行計畫（含同一查詢的多版本）
• 計畫強制（plan forcing）：把指定計畫釘死
• 查詢提示強制（query hint forcing）：在不改程式碼的前提下注入 hint

Azure SQL Database 從一開始就預設啟用，SQL Server 2022 起也預設啟用。SQL Server 2025 的 readable secondary 也預設啟用。

ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;

運作方式#

flowchart LR
    Q[Query] --> O[Optimizer]
    O --> PC[Plan Cache]
    PC -.async.-> M[Query Store memory]
    M -.async.-> ST[(Query Store system tables)]
    Q --> X[Execute]
    X --> RM[Runtime metrics memory]
    RM -.async.-> ST

關鍵觀念：

• 寫入完全 非同步（asynchronous） 以降低開銷
• 預設 15 分鐘 把記憶體資料寫到磁碟一次
• 預設 60 分鐘 為一個聚合區間
• 資料隨資料庫 備份／還原——換句話說，搬資料庫帶得走歷史

系統異常崩潰可能丟失「在記憶體但尚未寫盤」的資料。控制關機 / failover 前 可以強制刷盤：

EXEC sys.sp_query_store_flush_db;

七張系統表#

查詢 Query Store#

找某個 stored procedure 的所有計畫#

SELECT qsq.query_id,
       qsq.object_id,
       qsqt.query_sql_text,
       qsp.plan_id,
       CAST(qsp.query_plan AS XML) AS QueryPlan
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_query_text AS qsqt
        ON qsq.query_text_id = qsqt.query_text_id
    JOIN sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
WHERE qsq.object_id = OBJECT_ID('dbo.MyProc');

Query Store 把計畫存成 NVARCHAR(MAX) 而非 XML（避免 XML 巢狀深度上限）。要在 SSMS 裡點開圖形計畫必須先 CAST AS XML。

查詢文字裡的參數宣告#

stored procedure 的參數宣告會被 內嵌在查詢文字最前面。例如：

(@ReferenceOrderID int)SELECT  p.Name, ... WHERE th.ReferenceOrderID = @ReferenceOrderID

這跟 procedure 的原始定義不同，要找 query 時用 LIKE 不容易精準命中。建議改用 object_id 或 query_hash。

比對「自動參數化」過的查詢#

sys.fn_stmt_sql_handle_from_sql_stmt(text, 2) 可以把一段 T-SQL 解出 SQL handle。但只能對自動參數化生效——對 prepared statement 與 stored procedure 都不行。

query_parameterization_type 欄位四種值：

• 0：沒有參數化
• 1：使用者自行參數化
• 2：簡單參數化
• 3：強制參數化

取得指定時段的 runtime 與 waits#

runtime metrics 是 以計畫為單位、以區間為單位 聚合：

DECLARE @CompareTime DATETIME = '2025-05-29 12:22';
SELECT CAST(qsp.query_plan AS XML),
       qsrs.count_executions,
       qsrs.avg_duration,
       qsrs.stdev_duration,
       qsws.wait_category_desc,
       qsws.avg_query_wait_time_ms
FROM sys.query_store_plan AS qsp
    JOIN sys.query_store_runtime_stats AS qsrs
        ON qsrs.plan_id = qsp.plan_id
    JOIN sys.query_store_runtime_stats_interval AS qsrsi
        ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
    LEFT JOIN sys.query_store_wait_stats AS qsws
        ON qsws.plan_id = qsrs.plan_id
           AND qsws.execution_type = qsrs.execution_type
           AND qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
WHERE qsp.plan_id = 11
  AND @CompareTime BETWEEN qsrsi.start_time AND qsrsi.end_time;

查 wait stats 時用 LEFT JOIN——Query Store 只記 > 1 ms 的等待，可能根本沒紀錄。

控制 Query Store#

清空與選擇性移除#

-- 清空所有資料
ALTER DATABASE AdventureWorks SET QUERY_STORE CLEAR;

-- 移除單一 query 或 plan
EXEC sys.sp_query_store_remove_query @query_id = @QueryId;
EXEC sys.sp_query_store_remove_plan  @plan_id  = @PlanID;

變更設定#

SELECT * FROM sys.database_query_store_options;
ALTER DATABASE AdventureWorks SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 200);

設定立即生效，無須重啟。所有設定皆為資料庫層級，要套到每個資料庫各自設定。

Capture Mode#

四種：

• All：抓所有查詢
• None：不抓新資料但保留既有資料；可繼續做 plan forcing
• Auto（舊行為）：執行 ≥ 3 次或執行時間 ≥ 1 秒才抓——常被用來搭配 plan cache 的 Optimize for Ad Hoc
• Custom（SQL Server 2019+ / Azure SQL Database）：可調 4 個門檻：
  • Execution Count
  • Stale Threshold
  • Total Compile CPU Time (ms)
  • Total Execution CPU Time (ms)

對工作量大、ad-hoc 多的系統，Custom 模式通常比 All 更划算——可以避免一堆只跑一次的查詢洗版 Query Store。

SSMS 內建報表#

啟用 Query Store 的資料庫會在 Object Explorer 多出一個 Query Store 資料夾，內建七張報表：

• Regressed Queries：因計畫變動導致效能退步的查詢
• Overall Resource Consumption：整體資源消耗（預設一個月）
• Top Resource Consuming Queries：最常用的一張
• Queries With Forced Plans
• Queries With High Variation：執行時間波動大的查詢，常伴隨多計畫
• Query Wait Statistics：依等待類型分解
• Tracked Queries：手動追蹤特定查詢的歷史

報表中可直接「Force / Unforce Plan」——點按鈕即可。

Plan Forcing（計畫強制）#

把某個 plan_id 釘成「這個查詢未來都用它」。

EXEC sys.sp_query_store_force_plan @query_id = 121, @plan_id = 11;

優化器仍照常編譯，但編完後會檢查是否有 forced plan，有就用 forced plan（前提是該計畫仍可行）。

Morally equivalent plan：罕見情況下優化器會用「幾乎一樣但不完全相同」的計畫——這是官方術語。

支援度：

• 大部分查詢都支援
• cursor 只有 fast forward 與 static 兩種支援
• 不支援的查詢會直接報錯

Plan forcing 在 reboot / failover 後仍會保留——是真正持久的設定。

Query Hint Forcing（強制查詢提示）#

Azure SQL Database / SQL Server 2022+ 才有。可以 不改程式碼 的情況下幫某個查詢套上 hint：

EXEC sys.sp_query_store_set_hints @query_id = 550,
     @value = N'OPTION(OPTIMIZE FOR UNKNOWN)';

Query hint 不是建議，是命令。它剝奪優化器的選擇權，必須極為謹慎——只在徹底測試後、做為最後手段使用。

-- 列出所有設了 hint 的查詢
SELECT qsqh.query_hint_id, qsqh.query_id, qsqh.query_hint_text, qsqh.source_desc
FROM sys.query_store_query_hints AS qsqh;

-- 移除
EXEC sp_query_store_clear_hints @query_id = 550;

hint forcing 不在 SSMS 報表內可見，必須查 sys.query_store_query_hints。

Optimized Plan Forcing（最佳化計畫強制）#

SQL Server 2022（compatibility level 160）以上的新功能。當編譯成本超過內部門檻時，Query Store 會在計畫的 XML 中存一份 replay script，後續編譯可走 replay 加速。

不適用的情境：

• 非 FULL 優化的查詢
• 分散式查詢
• 帶 RECOMPILE hint 的查詢

-- 關閉
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF;

-- 查哪些 plan 有 replay script
SELECT qsqt.query_sql_text,
       TRY_CAST(qsp.query_plan AS XML) AS query_plan,
       qsp.is_forced_plan
FROM sys.query_store_plan AS qsp
    INNER JOIN sys.query_store_query AS qsq ON qsp.query_id = qsq.query_id
    INNER JOIN sys.query_store_query_text AS qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsp.has_compile_replay_script = 1;

升級時的安全網#

Fritchey 強調這是 Query Store 最強大但常被忽視 的用途之一。傳統升級流程是：到測試環境 → 跑測試 → 上 production，但容易漏抓某個 query 因新 CE 變慢的情況。

Query Store 的補救流程：

1. 升級前先在 SQL Server 較新版本 保留舊 compatibility level，讓 Query Store 蒐集現有計畫
2. 把 compatibility level 切到新版
3. Query Store 會記錄新版下的新計畫
4. 用 Regressed Queries 報表找出退步的查詢
5. 對退步查詢使用 plan forcing 釘住舊計畫，先穩住線上效能
6. 再回頭分析根因（CE 改變、新優化策略不適用等），逐一修正、解除 forcing

這套流程把「升級失敗」從「全部回滾」降級為「個別查詢回滾」。

本章定調#

• Query Store = 查詢效能的歷史檔案 + 計畫操控工具
• 預設啟用後幾乎零維護成本，記得在大流量資料庫適度設定 Capture Mode 與 MAX_STORAGE_SIZE
• Plan forcing 是診斷期間的快速安全閥；query hint forcing 是不改程式的應急手段
• 升級時把 Query Store 當作安全網，不只是事後分析工具

下一章將深入 執行計畫快取 的行為——理解計畫如何進入、停留、被踢出 plan cache，是優化重用率的關鍵。