您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

同樣 SQL 改寫參數化後效能暴跌 7 倍：參數嗅探與計畫快取陷阱深度解析

編輯：關於SqlServer

實測案例：將字串常數 `'3810'` 改為變數 `@mis_company_code` 後，執行時間從 1 秒飆升至 7 秒。根因：**參數嗅探** 導致優化器為變數產生「通用計畫」，而非常數的「最佳計畫」。

現場還原


-- 版本 A：常數 (1 秒)
SELECT ACC_SEGMENT
FROM dbo.mis_gl_balance
WHERE LEFT(ACC_SEGMENT, 22) IN (
    SELECT '3810.105301.' + mis_code
    FROM finance_budget_account
    WHERE budget_type = '電路租費'
);

-- 版本 B：變數 (7 秒)
DECLARE @mis_company_code CHAR(4) = '3810';
SELECT ACC_SEGMENT
FROM dbo.mis_gl_balance
WHERE LEFT(ACC_SEGMENT, 22) IN (
    SELECT @mis_company_code + '.105301.' + mis_code
    FROM finance_budget_account
    WHERE budget_type = '電路租費'
);

核心機制：參數嗅探

**常數/字面值**：優化器編譯時**看得到實際值**，利用統計資訊估算基數，選擇最佳索引/連接策略 (如 Nested Loops + Index Seek)。
**區域變數/參數**：優化器編譯時**看不到執行時值**，只能用**密度向量**估算 (假設均勻分布)，往往產生保守計畫 (如 Hash Match + Index Scan)。
預存程序參數預設**嗅探首次執行值**產生計畫並快取，後續不同參數值複用此計畫可能極不適配。

五大解決方案 (由輕到重)

方案	語法/做法	優點	缺點/風險
1. OPTION (RECOMPILE)	查詢尾加 `OPTION (RECOMPILE)`	每次編譯看見實際值，零快取污染	編譯 CPU 開銷，高頻查詢慎用
2. OPTIMIZE FOR (@param = '典型值')	`OPTION (OPTIMIZE FOR (@mis_company_code = '3810'))`	指定優化目標值，計畫可重用	若實際值分布差異大仍會失誤
3. OPTIMIZE FOR UNKNOWN	`OPTION (OPTIMIZE FOR (@mis_company_code UNKNOWN))`	強制用密度向量，行為穩定	放棄嗅探優勢，可能非最佳
4. 動態 SQL + sp_executesql	將變數內嵌為字面值組裝 SQL 字串執行	編譯時見到實際值，計畫可針對性快取	需防注入，代碼較複雜
5. 參數化改為常數 (僅限極少數固定值)	用 `IF @code='3810' SELECT ... ELSE SELECT ...`	各分支各自最佳計畫	值多時維護困難，違反 DRY

實戰建議流程

確認是參數嗅探問題：對比常數 vs 變數執行計畫 (關注 Estimated Rows vs Actual Rows、運算子差異)。
優先嘗試 `OPTION (RECOMPILE)` (低頻報表) 或 `OPTIMIZE FOR` (高頻 OLTP)。
若查詢極其複雜且參數值分布極不均勻 → 動態 SQL。
長期：建立過濾索引、統計資料直方圖、考慮強制參數化資料庫選項 (謹慎)。

延伸：預存程序參數嗅探


CREATE PROCEDURE GetBalance @CompanyCode CHAR(4)
AS
SELECT ... WHERE CompanyCode = @CompanyCode
OPTION (OPTIMIZE FOR (@CompanyCode = '3810')); -- 或 RECOMPILE

理解參數嗅探機制，善用查詢提示與動態 SQL，即可避免「改成參數化就變慢」的反直覺陷阱。