上篇介紹了查詢優化器與計劃緩存的衝突。引入緩存後,完整流程為:
緩存查找使用 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 驗證)。
數據庫級別設置:
ALTER DATABASE [CurrentDB] SET PARAMETERIZATION FORCED;
效果:字面值自動參數化(@0, @1...),緩存命中率大幅提升。
副作用:優化器無法利用統計信息直方圖針對特定值優化(如參數嗅探問題、索引選擇偏差)。
語句級強制重編譯,不緩存計劃:
SELECT * FROM Sales.Customer
WHERE CustomerID > 20000 AND TerritoryID = 4
OPTION (RECOMPILE);
適用場景:OLAP 複雜查詢、編譯成本遠低於錯誤計劃執行成本、參數敏感度極高。
指定優化器假設的參數值生成計劃並緩存:
DECLARE @vari INT = 4;
SELECT * FROM Sales.Customer
WHERE CustomerID > 20000 AND TerritoryID = @vari
OPTION (OPTIMIZE FOR (@vari = 4));
進階:OPTIMIZE FOR UNKNOWN(SQL 2008+)使用統計信息密度向量而非變量值優化,適合參數值分佈均勻場景。
CREATE PROC TestForLocalVari @vv INT AS
BEGIN
DECLARE @vari INT = @vv; -- 打破參數嗅探
SELECT * FROM Sales.Customer WHERE CustomerID > 20000 AND TerritoryID = @vari;
END
優點:計劃穩定(基於平均密度估算);缺點:估算不準確,不推薦常規使用。
無法修改應用代碼時,由 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 必須與實際提交批次逐字符完全匹配(含空格換行)。
數據庫強制參數化下,特定語句退回簡單參數化:
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)';
ANSI_NULLS, QUOTED_IDENTIFIER 等) → 導致同語句不同 SET 選項產生多份計劃可通過 sys.dm_exec_plan_attributes 查看計劃屬性差異。
| 目標 | 手段 | 代價 |
|---|---|---|
| 減少計劃數量、提高復用 | 強制參數化、計劃指導 | 可能產生非最優計劃 |
| 獲得最優計劃 | RECOMPILE、OPTIMIZE FOR | 增加編譯 CPU、不緩存/固定計劃 |
| 穩定性優先 | 局部變量、OPTIMIZE FOR UNKNOWN | 估算偏離實際分佈 |
SQL Server: Plan Cache - Cache Hit Ratio)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';
執行計劃緩存管理是「復用收益」與「優化精度」的動態平衡。沒有銀彈,唯有基於負載特徵、數據分佈、業務容忍度的持續觀測與調優。