萬盛學電腦網

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

SQL Server 執行計劃緩存深度解析(下):參數化、重編譯、計劃指導與優化策略

回顧與流程圖

上篇介紹了查詢優化器與計劃緩存的衝突。引入緩存後,完整流程為:

  1. 解析語句 → 生成查詢樹
  2. 查找計劃緩存(Hash 匹配)
  3. 命中 → 直接執行;未命中 → 編譯優化 → 存入緩存 → 執行

緩存查找使用 Hash 表(sys.dm_os_memory_cache_hash_tables),Bucket 過載會導致掃描鏈過長,降低匹配效率。

核心問題:參數化與非參數化的博弈

同邏輯不同字面值的查詢(如 WHERE ID=1 vs WHERE ID=2),默認生成獨立計劃,導致計劃緩存膨脹。

現象演示

SELECT * FROM Sales.Customer WHERE CustomerID > 20000 AND TerritoryID = 1;
SELECT * FROM Sales.Customer WHERE CustomerID > 20000 AND TerritoryID = 4;

雖然最優計劃相同,但緩存存儲兩份副本(sys.dm_exec_cached_plans 驗證)。

解決方案工具箱

1. 強制參數化

數據庫級別設置:

ALTER DATABASE [CurrentDB] SET PARAMETERIZATION FORCED;

效果:字面值自動參數化(@0, @1...),緩存命中率大幅提升。

副作用:優化器無法利用統計信息直方圖針對特定值優化(如參數嗅探問題、索引選擇偏差)。

2. OPTION (RECOMPILE)

語句級強制重編譯,不緩存計劃:

SELECT * FROM Sales.Customer 
WHERE CustomerID > 20000 AND TerritoryID = 4 
OPTION (RECOMPILE);

適用場景:OLAP 複雜查詢、編譯成本遠低於錯誤計劃執行成本、參數敏感度極高。

3. OPTIMIZE FOR 提示

指定優化器假設的參數值生成計劃並緩存:

DECLARE @vari INT = 4;
SELECT * FROM Sales.Customer 
WHERE CustomerID > 20000 AND TerritoryID = @vari 
OPTION (OPTIMIZE FOR (@vari = 4));

進階:OPTIMIZE FOR UNKNOWN(SQL 2008+)使用統計信息密度向量而非變量值優化,適合參數值分佈均勻場景。

4. 存儲過程局部變量替換參數

CREATE PROC TestForLocalVari @vv INT AS
BEGIN
    DECLARE @vari INT = @vv;  -- 打破參數嗅探
    SELECT * FROM Sales.Customer WHERE CustomerID > 20000 AND TerritoryID = @vari;
END

優點:計劃穩定(基於平均密度估算);缺點:估算不準確,不推薦常規使用。

5. 計劃指導

無法修改應用代碼時,由 DBA 為特定語句綁定提示:

EXEC sp_create_plan_guide 
    @name = N'MyPlanGuide1',
    @stmt = N'SELECT * FROM Sales.Customer WHERE CustomerID > 20000 AND TerritoryID = @vari',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@vari int',
    @hints = N'OPTION (RECOMPILE)';

關鍵限制:@stmt 必須與實際提交批次逐字符完全匹配(含空格換行)。

6. 模板計劃指導:單條語句簡單參數化

數據庫強制參數化下,特定語句退回簡單參數化:

DECLARE @stmt NVARCHAR(MAX), @params NVARCHAR(MAX);
EXEC sp_get_query_template 
    N'SELECT * FROM Sales.Customer WHERE CustomerID > 20000 AND TerritoryID = 2',
    @stmt OUTPUT, @params OUTPUT;

EXEC sp_create_plan_guide 
    @name = N'MyTemplatePlanGuide',
    @stmt = @stmt,
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = @params,
    @hints = N'OPTION (PARAMETERIZATION SIMPLE)';

自動重編譯觸發條件

  • 架構變更(DDL:改表名、刪列、改類型)
  • 統計信息更新(自動/手動)
  • 會話 SET 選項變化(ANSI_NULLS, QUOTED_IDENTIFIER 等) → 導致同語句不同 SET 選項產生多份計劃

可通過 sys.dm_exec_plan_attributes 查看計劃屬性差異。

平衡策略總結

目標手段代價
減少計劃數量、提高復用強制參數化、計劃指導可能產生非最優計劃
獲得最優計劃RECOMPILE、OPTIMIZE FOR增加編譯 CPU、不緩存/固定計劃
穩定性優先局部變量、OPTIMIZE FOR UNKNOWN估算偏離實際分佈

實戰建議

  1. 默認簡單參數化,觀察緩存命中率(SQL Server: Plan Cache - Cache Hit Ratio)
  2. 高頻骨架查詢 + 參數分佈均勻 → 保持參數化
  3. 高頻骨架查詢 + 參數分佈傾斜(熱點值) → OPTIMIZE FOR 熱點值 或 RECOMPILE
  4. 低頻複雜報表查詢 → RECOMPILE
  5. 第三方應用無法改代碼 → 計劃指導
  6. 定期清理無用計劃:DBCC FREEPROCCACHE(謹慎)或針對性 DBCC FLUSHPROCINDB

監控查詢

-- 緩存命中率
SELECT cntr_value AS CacheHitRatio
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cache Hit Ratio' AND object_name LIKE '%Plan Cache%';

-- 計劃緩存大小與條目數
SELECT 
    SUM(size_in_bytes) / 1024.0 / 1024.0 AS TotalSize_MB,
    COUNT(*) AS PlanCount
FROM sys.dm_exec_cached_plans;

-- 單 Bucket 平均掃描長度(過長提示 Hash 衝突)
SELECT buckets_avg_scan_miss_length
FROM sys.dm_os_memory_cache_hash_tables
WHERE name = 'SQL Plans';

結語

執行計劃緩存管理是「復用收益」與「優化精度」的動態平衡。沒有銀彈,唯有基於負載特徵、數據分佈、業務容忍度的持續觀測與調優。

copyright © 萬盛學電腦網 all rights reserved