SQL Server 效能調優實戰筆記 (一)：參數化與字面值產生的計畫差異 — 「傻瓜機」失效效應

同一 SQL 陳述式，在 SSMS 直接執行 < 1 秒，透過應用程式 sp_executesql 參數化執行卻需 59 秒。差異在於 字面值內嵌 vs 參數化 導致最佳化器擁有不同資訊量。

案例查詢

SELECT ISNULL(a.CWB_NO,b.CWB_NO) AS CWB_NO, ...
FROM (SELECT * FROM TB_CWB WHERE IMPORT_AWB_NO = @AWB_NO) a
FULL JOIN (SELECT * FROM TP_DECSUMMARY WHERE AWB_NO = @AWB_NO) b
  ON a.CWB_NO = b.CWB_NO AND b.AVAILABLE = 'Y'
WHERE a.AVAILABLE = 'Y'

現象解析

• SSMS 直接執行：參數值在批次中以字面值 '25200000011' 出現，最佳化器讀取直方圖精準估算，選擇最佳索引查找計畫。
• 應用程式 sp_executesql：參數化為 @AWB_NO，最佳化器編譯時未知具體值，依平均密度估算，可能選擇掃描或次優索引，導致嚴重效能落差。

「傻瓜機」失效效應

最佳化器像「傻瓜相機」：資訊充足 (字面值) 時拍出好照片 (優良計畫)；資訊不足 (參數化) 時只能用自動模式 (平均密度)，容易翻車。

解決路徑

1. OPTION (RECOMPILE)：編譯時攔截實際參數值，獲得字面值級估算（適用低頻、高變異查詢）。
2. OPTIMIZE FOR (@AWB_NO = '典型值')：引導最佳化器使用特定值估算（需了解典型值分佈）。
3. 動態 SQL 精確建構：僅在確知參數值時內嵌字面值，其他條件參數化（需防注入）。
4. 查詢存放區強制計畫 (SQL 2016+)：鎖定優良計畫避免退化。

結語

參數化帶來計畫重用與安全，但也遮蔽了最佳化器所需的值資訊。理解這一權衡，針對關鍵查詢施以 RECOMPILE 或提示，才能兼得安全與效能。