萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2016 查詢存放區實戰:偵測計畫退化與強制執行計畫完整教學

查詢存放區:解決「計畫退化」的終極武器

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。優化器正確判斷無選擇性,掃描更划算。

執行計畫:叢集索引掃描 419 邏輯讀

模擬服務重啟/計畫驅逐

DBCC FREEPROCCACHE; -- 僅測試環境使用!

第二次執行:小範圍參數 → 書籤查找(最佳)

EXEC RetrieveCustomers 1; -- 回傳 1 列

執行計畫:非叢集索引搜尋 + 書籤查找,邏輯讀取極低。

執行計畫:索引搜尋+書籤查找

第三次執行:大範圍參數卻重用書籤查找計畫 → 災難

EXEC RetrieveCustomers 80000; -- 參數嗅探導致重用錯誤計畫

結果:邏輯讀取暴增至 160,139!執行計畫仍為書籤查找,但實際處理 80,000 列,書籤查找成本極高。這就是經典的參數嗅探導致計畫退化。

使用查詢存放區診斷與修正

SSMS 圖形化報表入口

物件總管 → 資料庫 → 查詢存放區 → 多種內建報表:

  • 前幾個資源使用查詢:依 CPU、持續時間、邏輯讀/寫、記憶體排序
  • 追蹤查詢:特定查詢隨時間效能趨勢
  • 整體資源耗用:資料庫層級資源消耗概覽

SSMS 查詢存放區報表節點

切換到「邏輯讀取次數」維度

最耗資源查詢排序

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

比較兩個計畫

查詢 7 的兩個計畫

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

  • 計畫 8:叢集索引掃描(好計畫,419 邏輯讀)
  • 計畫 7:索引搜尋 + 書籤查找(壞計畫,16萬+ 邏輯讀)

計畫 8 叢集索引掃描

一鍵強制好計畫

在報表中點選 「強制執行計畫」 按鈕(針對計畫 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_mb
  • 手動清理:ALTER DATABASE ... SET QUERY_STORE CLEAR; 或依查詢 ID 刪除
  • 唯讀模式:空間滿時自動轉為 READ_ONLY,停止收集新資料
  • 容錯移轉/可用性群組:查詢存放區資料隨資料庫同步,故障轉移後依然可用

查詢存放區讓「計畫退化」從無頭公案變成可視化、可量化、可一鍵緩解的問題。但記住:效能調校的終點永遠是解決根因,而非長期依賴強制計畫。善用它作為診斷工具,再回頭優化索引與查詢寫法,才是正道!

copyright © 萬盛學電腦網 all rights reserved