SQL Server 2016 Query Store 實戰：偵測計畫回歸與強制執行計畫

啟用 Query Store

CREATE DATABASE QueryStoreDemo;
ALTER DATABASE QueryStoreDemo SET QUERY_STORE = ON;
ALTER DATABASE QueryStoreDemo SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 1,
    MAX_STORAGE_SIZE_MB = 100,
    QUERY_CAPTURE_MODE = ALL,
    SIZE_BASED_CLEANUP_MODE = OFF
);

重現計畫回歸

1. 建立表 Customers、非叢集索引 idx_Test(Value)、灌入 8 萬筆。
2. 建立預存程序 RetrieveCustomers @Value INT：SELECT * FROM Customers WHERE Value < @Value。
3. 首次執行 EXEC RetrieveCustomers 80000 → 優化器選擇 叢集索引掃描（419 邏輯讀）。
4. DBCC FREEPROCCACHE 模擬重啟/計畫驅逐。
5. 執行 EXEC RetrieveCustomers 1 → 產生 Key Lookup 計畫並快取。
6. 再次 EXEC RetrieveCustomers 80000 → 復用 Key Lookup 計畫 → 16 萬邏輯讀，效能崩潰。

Query Store 偵測與修正

1. SSMS 物件總管 → 資料庫 → Query Store → 「資源耗用最高的查詢」。
2. 切換度量為「邏輯讀取」，找到查詢 ID 7，右側顯示兩個計畫 ID 7（Key Lookup）與 8（叢集掃描）。
3. 點選計畫 8 → 「強制執行計畫」。

驗證

EXEC RetrieveCustomers 80000;  -- 回歸叢集掃描，419 邏輯讀

關鍵警示

強制計畫只是止血，非根治！
根因：參數嗅探導致計畫不穩定。長期解決：

• 建立 覆蓋索引（含所有 SELECT 欄位）消除 Key Lookup，使單一計畫適用所有參數值。
• 或使用 OPTION (RECOMPILE)、OPTIMIZE FOR、OPTIMIZE FOR UNKNOWN 控制編譯行為。
• Query Store 另一大價值：歷史保留（重啟後仍可回溯），解決傳統計畫快取易失痛點。