什麼是參數探嗅（Parameter Sniffing）#

當一個 stored procedure / prepared statement 第一次編譯時，優化器會看那次傳進來的真實參數值，用它去查 statistics histogram，得到精確的列數估算，據此產生計畫。

這個機制叫 parameter sniffing——多數時候它能讓計畫更貼近真實資料。

Parameter sniffing 不是 bug，是預設特性。問題是：被 sniff 的那次參數，可能與後續呼叫的參數差很多。

同一條 procedure，不同參數有時回 2 列、有時回 70,000 列——但快取裡只有一份計畫。這就是 bad parameter sniffing。

Parameter vs. Variable#

兩者長得像，行為不同：

-- parameter（procedure 定義的一部分）
CREATE PROCEDURE dbo.AddressList (@City VARCHAR(30)) AS ...

-- variable（procedure 內部宣告）
DECLARE @City VARCHAR(30);

• Parameter：編譯時值已知 → 被 sniff
• Variable：編譯時值未知 → 用 statistics 的「平均值」估算

例外：發生 statement-level recompile 時，variable 的值就變成已知，這時也會被 sniff。

Bad Sniffing 的具體例子#

書中以 Production.TransactionHistory 上的 ReferenceOrderID 欄位為例——資料分佈嚴重不均：

• ReferenceOrderID = 53465 → 回 72 列
• ReferenceOrderID = 816 → 回 2 列

當「為大資料量編的計畫被小資料量複用」或反過來時，效能就崩。

用 variable 取代 parameter 的影響#

DECLARE @ReferenceOrderID INT = 53465;
SELECT ... WHERE th.ReferenceOrderID = @ReferenceOrderID;

優化器無法 sniff，使用 平均值估算（average）——估算列數是 3，產出一個「平均型」計畫。對 72 列的查詢執行時間反而更快（634μs）但 reads 翻倍（362）。取捨在哪裡決定於系統的瓶頸。

找出參數敏感的查詢#

問題的特性：間歇性效能異常——好一段時間後突然變慢，又自己恢復。原因通常是計畫被踢出快取，下一次重新編譯時剛好用到「不適合多數情境」的參數。

要診斷，必須拿到 同一查詢的多份計畫。三條路：

1. Plan Cache + DMV#

SELECT deps.execution_count,
       deps.total_elapsed_time,
       deps.total_logical_reads,
       deqp.query_plan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
WHERE deps.object_id = OBJECT_ID('dbo.MyProc');

計畫被踢出 cache 就拿不到了。

2. Query Store#

SELECT SUM(qsrs.count_executions) AS ExecutionCount,
       AVG(qsrs.avg_duration) AS AvgDuration,
       AVG(qsrs.avg_logical_io_reads) AS AvgReads,
       CAST(qsp.query_plan AS XML) AS QueryPlan,
       qsp.query_id, qsp.plan_id
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qsrs ON qsrs.plan_id = qsp.plan_id
WHERE qsq.object_id = OBJECT_ID('dbo.MyProc')
GROUP BY qsp.query_plan, qsp.query_id, qsp.plan_id;

Query Store 是診斷 bad sniffing 的 首選——會自動保留所有計畫版本，配合 Regressed Queries / High Variation 報表立刻看出來。

3. Extended Events#

query_post_execution_showplan 事件可同時抓計畫與 runtime metrics——但 昂貴，只能短期啟用。

找出對應的參數值#

打開計畫的 第一個運算子 → Properties → Parameter List，可看到：

• Compile-time value
• Runtime value

差很多 → 高度可疑。

對照到統計資訊（Histogram）#

光知道參數值不夠，要知道 histogram 怎麼解讀它們：

SELECT range_high_key, equal_rows, average_range_rows
FROM sys.dm_db_stats_histogram(OBJECT_ID('Production.TransactionHistory'), 3);

• range_high_key 等於某個值 → 用 equal_rows（精確）
• 落在 step 之間 → 用 average_range_rows（平均）

書中例子：

• 816 不等於任何 boundary → 用 average_range_rows = 2.266
• 53465 等於某個 boundary → equal_rows = 72

差距 70:1 → 計畫絕對不同。

解法#

Fritchey 列出六種解法，除了 PSP（最後一種）以外都帶取捨：

• 完全停用 parameter sniffing
• 用 local variable 取代 parameter
• 每次執行都 recompile
• 用 OPTIMIZE FOR query hint
• 用 Query Store 的 plan forcing
• Multiplan（PSP / OPO）：條件達標時自動產出多計畫

1. 停用 Parameter Sniffing#

三種粒度：

-- 全伺服器（不建議）
DBCC TRACEON (4136, -1);

-- 整個資料庫（2016+ / Azure SQL）
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

-- 單一查詢
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

完全停用會 失去 sniffing 的好處——大多數查詢從中受益，僅少數受害。粒度越大、影響越廣，請優先選 USE HINT。

2. Local Variable（不建議）#

DECLARE @LocalCity VARCHAR(30) = @City; -- 用 local 取代 parameter
SELECT ... WHERE A.City = @LocalCity;

效果：強制走「平均值」計畫。這是 SQL Server 2000 時代的做法，現代有更好的工具。看到舊程式這樣寫請加註解避免後人困惑。

3. RECOMPILE#

... WHERE th.ReferenceOrderID = @ReferenceOrderID
OPTION (RECOMPILE);

每次執行都重新編譯——每次都得到對 本次參數 最佳的計畫。

兩個代價：

• 每次執行付編譯成本（OLTP 高頻場景殺傷力大）
• 計畫不會被快取——也意味 DMV 看不到歷史

高頻短查詢避免使用；低頻、資料分佈極不均的查詢可考慮。

4. OPTIMIZE FOR#

OPTION (OPTIMIZE FOR (@ReferenceOrderID = 53465))
-- 或
OPTION (OPTIMIZE FOR (@ReferenceOrderID UNKNOWN))

• 指定值：強制以特定值編譯——選一個「對多數情境最佳」的代表值
• UNKNOWN：強制走「平均值」計畫，等同於使用 local variable 的效果

多數情境下 OPTIMIZE FOR ... UNKNOWN 是 RECOMPILE 與停用 sniffing 之間的平衡折衷——可重用、不必每次重編、又能避開極端值。

5. Query Store Plan Forcing#

第 6 章介紹過。對某個 query 釘住一個你驗證過好用的計畫——不用改程式碼。

突發 bad sniffing 的 第一反應：在 Query Store 找出表現好的舊計畫 → force 起來止血 → 再回頭分析根因（資料分佈、索引、統計、query 結構）。

6. Parameter-Sensitive Plan Optimization（PSP / Multiplan）#

SQL Server 2022+（compatibility level 160）的新功能。優化器自動偵測「對參數值敏感」的查詢，為同一查詢編譯多個計畫——例如：

• 計畫 A：適合「值偏小、結果集少」
• 計畫 B：適合「值偏大、結果集多」

執行時根據 dispatcher plan 動態挑選。

PSP 是 唯一不需要妥協 的解法——它 既保留 sniffing 好處、又自動處理變動。但有資格條件：

• 對欄位上的統計傾斜程度有要求
• 不是所有 query 都能 PSP
• 觀察計畫是否被識別為 multi-plan：看執行計畫上是否有 Dispatcher

PSP 與 OPO（Optional Parameter Optimization，處理 WHERE @Foo IS NULL OR Col = @Foo 模式）行為類似，都是「同查詢多計畫」。

解決後仍要追蹤#

任何方案都不是「一勞永逸」。資料隨時間變動，今天有效的解法明天可能變反向問題。

持續監測 Query Store 的 Regressed Queries 與 High Variation 報表，是長期治理的關鍵。

本章定調#

• Parameter sniffing 是預設特性、多半有益；少數情境變成 bad sniffing
• 診斷靠 Query Store 拿到多份計畫 + Parameter List + histogram 對照
• 解法依粒度與代價排序：先嘗試 PSP / OPTIMIZE FOR ... UNKNOWN / Query Store force，再考慮 RECOMPILE，最後才是停用 sniffing
• 修完還是要持續監測

下一章將進入 查詢設計分析——從 T-SQL 寫法本身找出效能殺手。