SQL Server 效能調優實戰筆記 (四)：複雜動態查詢的參數嗅探與計畫失效分析

上線系統出現主鍵查詢卻耗時 10~18 秒，排除硬體、鎖定、索引碎片後，鎖定為動態 SQL 產生的執行計畫不穩定問題。

問題查詢片段

WHERE a.AVAILABLE = 'Y'
  AND (a.CWB_NO = @CWB_NO OR
       (@CWB_NO IS NULL AND a.AWB_NO = @AWB_NO OR @AWB_NO IS NULL)
       AND (a.BWB_NO = @BWB_NO OR @BWB_NO IS NULL)
       ... 多個選擇性條件 ...)

根因：參數嗅探與「萬用查詢」反模式

• 單一預存程序/動態 SQL 試圖以 OR @Param IS NULL 涵蓋所有查詢組合。
• 首次編譯時依特定參數值嗅探產生計畫（如走索引查找），後續不同參數組合復用該計畫導致掃描全表或估算嚴重偏差。
• 統計資料未及時更新加劇誤估。

解決策略

1. 拆分為多個專用查詢：依業務入口建立不同預存程序/動態 SQL 區塊，各自最佳化。
2. OPTION (RECOMPILE)：針對低頻、高變異查詢強制重編譯，獲得最新參數值計畫。
3. 動態 SQL 建構精確 WHERE 子句：僅串接實際傳入非 NULL 的條件，避免 OR @Param IS NULL 邏輯。
4. 更新統計資料 / 建立篩選索引：針對高選擇性欄位建立 WHERE AVAILABLE = 'Y' 篩選索引。
5. 查詢存放區 (Query Store)：SQL 2016+ 可追蹤計畫變更並強制優良計畫。

驗證步驟

1. 擷取實際執行計畫 (SET STATISTICS XML ON) 對比估算/實際資料列。
2. 檢視 sys.dm_exec_query_stats 找出高 CPU/IO/持續時間查詢。
3. 使用 DBCC FREEPROCCACHE (謹慎) 或 sp_recompile 觀察重編譯後表現。

結語

「一個查詢打天下」的動態 SQL 往往是效能殺手。拆分查詢路徑、善用重編譯提示、精確建構動態 SQL，配合持續統計資料維護，才能穩定高效能。