SQL Server 效能調優實錄：參數嗅探導致執行計畫失誤與重寫優化

案例背景

某物流系統查詢運單明細，主表 TB_CWB (數百萬列)、從表 TB_CWBWEIGHT。透過預存程序參數 @CWB_NO 查詢單筆，但執行時間 7-16 秒；改為字面值常數後 < 1 秒。根因：**參數嗅探** 導致優化器依據參數值選擇「大資料量並行計畫」而非「單筆索引搜尋」。

原始慢速 SQL (參數化)

DECLARE @CWB_NO VARCHAR(50) = '31301379874';
SELECT a.AWB_NO, a.BWB_NO, a.CWB_NO, a.ORIGIN, a.DEST, a.MODIFY_ON, a.CREATED_ON,
       a.CONSIGNOR_CUSTOMER_CODE, a.CONSIGNOR_CODE, a.CONSIGNOR_NAME, a.CONSIGNEE_NAME,
       a.CWB_STATUS, a.CWB_TYPE, ISNULL(a.PCS,0) AS PCS, a.BWBLIST, b.PWEIGHT
FROM TB_CWB AS a
LEFT JOIN TB_CWBWEIGHT AS b ON a.CWB_NO = b.CWB_NO AND b.AVAILABLE = 'Y'
WHERE a.AVAILABLE = 'Y'
  AND (a.CWB_NO = @CWB_NO OR
       (@CWB_NO IS NULL AND a.AWB_NO = NULL OR NULL IS NULL AND ... 多條件動態查詢模式 ...));

執行計畫特徵 (圖 1)

圖 1：並行掃描 + 雜湊聯結計畫 (預估大資料量)

• 叢集索引掃描 (Parallel) → 篩選 → 雜湊聯結 (Parallel)
• 優化器依據參數 @CWB_NO 的密度向量預估回傳大量列，選擇並行計畫。
• 實際僅 1 列，並行啟動開銷、雜湊建立探查成本遠超單執行緒索引搜尋。

優化後快速 SQL (字面值常數)

SELECT a.AWB_NO, a.BWB_NO, a.CWB_NO, a.ORIGIN, a.DEST, a.MODIFY_ON, a.CREATED_ON,
       a.CONSIGNOR_CUSTOMER_CODE, a.CONSIGNOR_CODE, a.CONSIGNOR_NAME, a.CONSIGNEE_NAME,
       a.CWB_STATUS, a.CWB_TYPE, ISNULL(a.PCS,0) AS PCS, a.BWBLIST, b.PWEIGHT
FROM TB_CWB AS a
LEFT JOIN TB_CWBWEIGHT AS b ON a.CWB_NO = b.CWB_NO AND b.AVAILABLE = 'Y'
WHERE a.AVAILABLE = 'Y'
  AND (a.CWB_NO = '31301379874' OR
       ('31301379874' IS NULL AND a.AWB_NO = NULL OR NULL IS NULL AND ...));

執行計畫特徵 (圖 2)

圖 2：巢狀迴圈聯結 + 索引搜尋計畫 (實際單列)

• 叢集索引搜尋 (Single Thread) → 巢狀迴圈聯結 → 索引搜尋
• 優化器識別字面值常數高選擇性，選擇最優單筆存取路徑。
• 實際執行 < 1 秒，效能提升 10-16 倍。

根因深度剖析：參數嗅探與動態查詢模式

五大修正策略 (由輕到重)

1. OPTION (RECOMPILE) —— 即時編譯，針對實際參數生成最優計畫

SELECT ... FROM ...
WHERE ...
OPTION (RECOMPILE); -- 每次執行重新編譯，CPU 開銷換取計畫精準

適用：執行頻率中低、參數選擇性變化大、可接受編譯開銷。

2. OPTION (OPTIMIZE FOR (@CWB_NO = '31301379874')) —— 指定優化參考值

SELECT ... FROM ...
WHERE ...
OPTION (OPTIMIZE FOR (@CWB_NO = '31301379874'));

適用：參數值分布已知、大多數查詢為高選擇性單值。

3. OPTION (OPTIMIZE FOR UNKNOWN) —— 忽略參數值，使用統計資訊平均密度

SELECT ... FROM ...
WHERE ...
OPTION (OPTIMIZE FOR UNKNOWN);

適用：參數值分布極不均勻、無明確代表值、避免極端計畫。

4. 區域變數指派 —— 切斷嗅探 (舊版相容寫法)

DECLARE @LocalCWB_NO VARCHAR(50) = @CWB_NO; -- 指派給區域變數
SELECT ... WHERE a.CWB_NO = @LocalCWB_NO ...; -- 優化器視為未知值，用平均密度估算

5. 動態 SQL (sp_executesql) —— 參數內嵌為字面值

DECLARE @SQL NVARCHAR(MAX) = N'SELECT ... WHERE a.CWB_NO = @CWB_NO ...';
EXEC sp_executesql @SQL, N'@CWB_NO VARCHAR(50)', @CWB_NO = @CWB_NO;
-- sp_executesql 會為不同參數值產生/快取獨立計畫 (參數化計畫)

最徹底方案：每個不同選擇性參數值獲得專屬計畫，但會增加計畫快取壓力。

架構層面建議：拆解巨型預存程序

原文結論：預存程序中不應撰寫冗長複雜的單一 SQL，應拆解為多個簡單陳述式，搭配暫存表/表變數暫存中間結果，讓優化器針對每個簡單步驟生成最優計畫，並便於除錯、維護、統計資訊更新。

重構模式範例

CREATE PROC usp_GetCWBDetail @CWB_NO VARCHAR(50)
AS
BEGIN
    -- 步驟 1：快速定位主鍵 (索引搜尋)
    SELECT CWB_NO, AWB_NO, BWB_NO, ... 
    INTO #TmpMain
    FROM TB_CWB 
    WHERE AVAILABLE = 'Y' AND CWB_NO = @CWB_NO;

    -- 步驟 2：關聯從表 (索引搜尋)
    SELECT m.*, w.PWEIGHT
    FROM #TmpMain m
    LEFT JOIN TB_CWBWEIGHT w ON m.CWB_NO = w.CWB_NO AND w.AVAILABLE = 'Y';
END

驗證清單

• ✅ 執行計畫從 Parallel Scan/Hash Join 變為 Index Seek/Nested Loops。
• ✅ 邏輯讀取 從 100,000+ 降至 < 100。
• ✅ CPU 時間 從 5000ms+ 降至 < 50ms。
• ✅ 計畫快取無「參數嗅探」警告 (查詢 sys.dm_exec_cached_plans 配合 query_plan XML 檢查 ParameterCompiledValue vs ParameterRuntimeValue)。

結語

參數嗅探是 SQL Server 優化器的雙面刃：善用則複用高效計畫，失效則導致災難性計畫錯配。掌握 OPTION (RECOMPILE/OPTIMIZE FOR)、動態 SQL、區域變數、拆解巨型查詢等工具箱，並建立「執行計畫異常 → 檢查參數嗅探 → 套用修正 → 驗證效能」的標準化調優流程，是 DBA 與開發者進階必修課。