DBA 最頭痛的情境:昨天跑得好好的查詢,今天突然變慢。根因多為計畫退化——優化器因參數嗅探、統計資料過期、索引變更等原因,選擇了效率較差的執行計畫並重複重用。SQL Server 2016 引入 查詢存放區,像飛機黑盒子一樣持久化記錄查詢文本、執行計畫、執行統計,跨服務重啟保留,讓你輕鬆回溯、比較、修正。
CREATE DATABASE QueryStoreDemo;
GO
USE QueryStoreDemo;
GO
-- 啟用
ALTER DATABASE QueryStoreDemo SET QUERY_STORE = ON;
GO
-- 進階配置
ALTER DATABASE QueryStoreDemo SET QUERY_STORE (
OPERATION_MODE = READ_WRITE, -- 讀寫模式(預設)
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367), -- 保留一年多
DATA_FLUSH_INTERVAL_SECONDS = 900, -- 15分鐘刷新一次磁碟(效能/持久性平衡)
INTERVAL_LENGTH_MINUTES = 1, -- 聚合統計間隔 1 分鐘(細緻度高)
MAX_STORAGE_SIZE_MB = 100, -- 最大儲存 100MB
QUERY_CAPTURE_MODE = ALL, -- 捕獲所有查詢
SIZE_BASED_CLEANUP_MODE = OFF -- 手動管理清理
);
GO
CREATE TABLE Customers (
CustomerID INT NOT NULL PRIMARY KEY CLUSTERED,
CustomerName CHAR(10) NOT NULL,
CustomerAddress CHAR(10) NOT NULL,
Comments CHAR(5) NOT NULL,
Value INT NOT NULL
);
GO
CREATE UNIQUE NONCLUSTERED INDEX idx_Test ON Customers(Value);
GO
-- 插入 80,000 筆資料
DECLARE @i INT = 1;
WHILE (@i <= 80000)
BEGIN
INSERT INTO Customers VALUES (@i, CAST(@i AS CHAR(10)), CAST(@i AS CHAR(10)), CAST(@i AS CHAR(5)), @i);
SET @i += 1;
END
GO
CREATE PROCEDURE RetrieveCustomers @Value INT AS
BEGIN
SELECT * FROM Customers WHERE Value < @Value;
END
GO
EXEC RetrieveCustomers 80000; -- 回傳 80,000 列
執行計畫:叢集索引掃描,邏輯讀取 419。優化器正確判斷無選擇性,掃描更划算。

DBCC FREEPROCCACHE; -- 僅測試環境使用!
EXEC RetrieveCustomers 1; -- 回傳 1 列
執行計畫:非叢集索引搜尋 + 書籤查找,邏輯讀取極低。

EXEC RetrieveCustomers 80000; -- 參數嗅探導致重用錯誤計畫
結果:邏輯讀取暴增至 160,139!執行計畫仍為書籤查找,但實際處理 80,000 列,書籤查找成本極高。這就是經典的參數嗅探導致計畫退化。
物件總管 → 資料庫 → 查詢存放區 → 多種內建報表:


查詢 ID 7 為我們的預存程序 SELECT 陳述式,捕獲到 兩個不同計畫 ID (7, 8)。

點選計畫 ID 可查看圖形化執行計畫:

在報表中點選 「強制執行計畫」 按鈕(針對計畫 8):

對應 T-SQL:
EXEC sp_query_store_force_plan @query_id = 7, @plan_id = 8;
EXEC RetrieveCustomers 80000;
再次執行,執行計畫回歸叢集索引掃描,邏輯讀取降回 419。問題暫時解決!
強制計畫僅治標不治本! 根因是「書籤查找計畫不穩定」,隨參數值劇烈波動。真正根治方案:
- 建立覆蓋索引(INCLUDE 所有 SELECT 欄位),消除書籤查找 → 計畫穩定為索引搜尋
- 或使用
OPTION (RECOMPILE)/OPTIMIZE FOR UNKNOWN/ 動態 SQL 避免參數嗅探- 更新統計資料、檢查索引碎片
SELECT * FROM sys.database_query_store_options; 監控 current_storage_size_mb / max_storage_size_mbALTER DATABASE ... SET QUERY_STORE CLEAR; 或依查詢 ID 刪除查詢存放區讓「計畫退化」從無頭公案變成可視化、可量化、可一鍵緩解的問題。但記住:效能調校的終點永遠是解決根因,而非長期依賴強制計畫。善用它作為診斷工具,再回頭優化索引與查詢寫法,才是正道!