深入 SQL Parameter Sniffing 與執行計劃效能問題

2023-11-10 10:37 PM

【前言】關於 SQL 執行計劃如何影響查詢效能議題很久之前研究過了(SQL筆記：Index Scan vs Index Seek、SQL筆記：Literal, Variable與Parameter)，昨天研究網站執行 Stored Procedure 爆慢，在 SSMS 秒回問題找到 SQL Server MVP Erland Sommarskog 解釋 Parameter Sniffing 議題的經典文章 - Slow in the Application, Fast in SSMS? (sommarskog.se)，在 Parameter Sniffing 議題上有深入剖析，並附有完整範例、檢測工具，不讀完對不起自己，完食整理筆記如下。
(以下假設大家已知道執行計劃、Parameter Sniffing、執行計劃快取、清除快取重新建立時機... 等概念，提到這些名詞時不再多做解釋)

開始前先釐清一個觀念，網站執行 Stored Procedure 爆慢，在 SSMS 秒回問題一文提到 SSMS 因 SET ARITHABORT ON 查詢比網站快，不是因為 ARITHABORT 改變查詢行為，純粹是因參數 ARITHABORT ON/OFF 差異導致 SSMS 使用了跟網站不同的執行計劃，而這個執行計劃比網站在用的執行計劃更適合當時的傳入參數，所以查詢效能良好。換言之，問題的核心在於 SQL 會進行 Parameter Sniffing 依據參數決定最適合執行計劃，當不同參數產生的執行計劃差異很大時，就可能發生用參數用 A 值推算的執行計劃，查詢 A 只要一秒，用來查詢 B 卻要一分鐘。因此，問題出執行計劃對不同參數的執行效能差異過大，ARITHABORT 差異有影響源自改用另一個較沒問題的查詢計劃避開，與 ARITHABORT 本身沒有關係 (引述 Erland 說法：Under normal circumstances there is absolutely no difference between ARITHABORT ON and OFF)，真正要解決的是「依參數值 A 產生執行計劃，用來查 B 效能會悲劇」這個問題。而要解決這類問題，必須了解 Parameter Sniffing 與執行計劃的關係，而 Erland 的文章是絕佳敲門磚。

當遇到 SP 因為快取的執行計劃不利於特定參數查詢而異常緩慢，最簡單粗暴的解法：EXEC sp_recompile problem_sp，如此可強制清掉快取，於下次查詢時重新產生執行計劃，若問題不再發生，結案。
如果不幸問題持續發生，代表你需要找出有問題的執行計劃，分析什麼參數值造成執行計劃不佳並設法改善。
偵查 Parameter Sniffing 問題前應先掌握以下情資：
- 是哪個指令慢？
- 快的跟慢的執行計劃分別長什麼樣子？
- SQL 依據哪些參數決定執行計劃？
- 資料表與索引結構如何？
- 資料分佈統計如何？是否有更新？
若狀況為 SSMS 快網站慢，同時取得快、慢執行計劃的方法是分別設 ARITHABORT ON 及 OFF 各跑一次：
```
SET ARITHABORT ON
go
EXEC that_very_sp 4711, 123, 1
go
SET ARITHABORT OFF
go
EXEC that_very_sp 4711, 123, 1
```
執行計劃最右邊的 SELECT/INSERT... 按右鍵選 Property 找 ParameterList / Parameter Compiled Value 即嗅探值(Sniffed Value)，多半就是 Parameter Sniffing 問題的來源：
執行計劃圖還有幾個地方可以留意，Cost [1] 數字愈高愈是瓶頸所在(但要注意它是預測值，有可能失真)，相對的箭頭會愈粗[2]，另外，實際筆數與預測筆數落差[3]也很具參考價值。

在無法使用 SSMS 的場合，以下這段 SQL 可取得執行計劃：(註：需 GRANT VIEW SERVER PERFORMANCE STATE)

DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)
SELECT @dbname = 'Northwind', -- DB 名稱 
       @procname = 'dbo.List_orders_2' -- SP 名稱

; WITH basedata AS (
   SELECT qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
   CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_start_offset,
                                            qs.statement_end_offset) qp
   CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
)
SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,
       CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
       CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
       CAST (query_plan AS xml) AS [Query plan]
FROM   next_level n
CROSS  APPLY   n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter

上述查詢可取得該 SP 的執行計劃(Query Plan 的 XML 用 SSMS 開啟即為執行計劃流程圖)及其對映嗅探值，非常方便：

(SET - ARITHABORT 等選項值組合、POS - 查詢指令在 SP 的起始位置(第幾個字元))

以下指令可用來清查資料表及索引：

DECLARE @tbl nvarchar(265)
SELECT @tbl = 'Orders'

SELECT o.name, i.index_id, i.name, i.type_desc,
       substring(ikey.cols, 3, len(ikey.cols)) AS key_cols,
       substring(inc.cols, 3, len(inc.cols)) AS included_cols,
       stats_date(o.object_id, i.index_id) AS stats_date,
       i.filter_definition
FROM   sys.objects o
JOIN   sys.indexes i ON i.object_id = o.object_id
OUTER  APPLY (SELECT ', ' + c.name +
                     CASE ic.is_descending_key
                          WHEN 1 THEN ' DESC'
                          ELSE ''
                     END
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.object_id = c.object_id
                                  AND ic.column_id = c.column_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id  = i.index_id
                AND  ic.is_included_column = 0
              ORDER  BY ic.key_ordinal
              FOR XML PATH('')) AS ikey(cols)
OUTER  APPLY (SELECT ', ' + c.name
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.object_id = c.object_id
                                  AND ic.column_id = c.column_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id  = i.index_id
                AND  ic.is_included_column = 1
              ORDER  BY ic.index_column_id
              FOR XML PATH('')) AS inc(cols)
WHERE  o.name = @tbl
ORDER  BY o.name, i.index_id

資料表的欄位、索引資訊一目了然：

最後是統計資料狀態：

DECLARE @tbl nvarchar(265)
SELECT @tbl = 'Orders'

SELECT o.name, s.stats_id, s.name, s.auto_created, s.user_created,
       substring(scols.cols, 3, len(scols.cols)) AS stat_cols,
       stats_date(o.object_id, s.stats_id) AS stats_date,
       s.filter_definition
FROM   sys.objects o
JOIN   sys.stats s ON s.object_id = o.object_id
CROSS  APPLY (SELECT ', ' + c.name
              FROM   sys.stats_columns sc
              JOIN   sys.columns c ON sc.object_id = c.object_id
                                  AND sc.column_id = c.column_id
              WHERE  sc.object_id = s.object_id
                AND  sc.stats_id  = s.stats_id
              ORDER  BY sc.stats_column_id
              FOR XML PATH('')) AS scols(cols)
WHERE  o.name = @tbl
ORDER  BY o.name, s.stats_id

stats_date 為最後更新時間，若年代久遠，則有可能是造成 Parameter Sniffing 問題的根源：

若確認統計資料過期，可用 UPDATE STATISTICS tblName 更新，它會以取樣方式更新，一般都能精準反映現況。若取樣更新結果不理想，再嘗試 UPDATE STATISTICS tblName WITH FULL SCAN, INDEX (加 INDEX 只更新索引統計，可縮執行時間)，若只更新特定索引，則可 UPDATE STATISTICS tblName indexName WITH FULLSCAN。更新統計會觸發重新編譯及 Parameter Sniffing，一般可馬上看到成效。

有一些情況會導致 Parameter Sniffing 相關效能問題反覆發生，例如：
1. 查詢寫法不適合使用 Parameter Sniffing，依據 A 參數值產生的執行計劃用在 B 參數很慘，反之亦然
2. 有少數參數對映的執行計劃與其他大部分參數完全不同，若先用它跑 Parameter Sniffing，則後面的查詢只剩一個慘字
3. 查詢條件沒有完美的索引可用，但有好幾個可用索引供挑選，挑到不同索引執行計劃效能差異很大實際狀況很多種，沒有一體適用的解法，需依個案分析解決。
因此，在程式加上 SET ARITHABORT ON 並不是真的解決問題，它類似 sp_recompile 會強迫用現在的參數重新產生執行計劃，若 Parameter Sniffing 導致不良執行計劃的狀況仍在，遲早會再發生。
案例：查指定客戶特定日期區間的訂單，客戶有很多訂單用 OrderDate Index Seek，若只有一筆用 CustomerId Index Seek：
大絕：在 SP 加上 OPTION (RECOMPILE) 強迫每次重新編譯，犧牲一點效能避開快取執行計劃不適用特定參數值的嚴重問題。但若呼叫頻率很高或 SP 複雜編譯耗時，要小心副作用反撲。
常見問題：動態搜尋條件通常不利於 Parameter Sniffing，如 WHERE (CustomerID = @custid OR @custid IS NULL) AND (OrderDate = @orderdate OR @orderdate IS NULL)...，可考慮 OPTION (RECOMPILE)。
查索引值分佈可用 DBCC SHOW_STATISTICS

以 1996-07-19 為例，RANGE_ROWS = 8 表有 8 筆資料介於 07/08 與 07/19 間(不含)，EQ_ROWS = 2 表有兩筆等於 7/19，DISTINCT_RANGE_ROWS = 8 表是 RANGE_ROWS 有 8 筆不同值，AVG_RANGE_ROWS = RANGE_ROWS / DISTINCT_RANGE_ROWS。
案例：索引放在 Col3，Col3 資料分佈不均勻，有些類別只有 17 筆，有些有 421,121 筆 (可用)，理論上不適合用於 Index Seek，但因為第一筆查詢參數是個不存在的分類，其預測值取 1，SQL 認定其是適合的索引，造成悲劇。
幾種解法：
1. OPTION (RECOMPILE)
2. 新增完全吻合該查詢條件的專用索引(注意佔用空間及增加資料更新負擔)
3. 調整或刪除 Col3 索引(用 sys.dm_db_index_usage_stats 佐證它很廢)
4. 加上索引提示，如 JOIN Table_A a WITH (INDEX = Combo_ix) ON a.Col4 = b.Col1
5. 加上 OPTIMIZE FOR OPTION (OPTIMIZE FOR (@p3 = 'KIWI'))
6. 將 Col3 比對參數轉成區域變數(避開 Sniffing)
7. 調整程式行為(例如：避免先用不存在的分類查詢)
SSMS 觀察執行計劃一般要等到執行完，若查詢很耗時，可使用 Live Query Plan 搶先看。(版本需求：SQL2014+/SSMS2016+)
選項位於 Query/Include Live Query Statistics

另外一個做法是從 Active Monitor / Active Expensive Queries 找到正在執行中查詢，由右鍵選單開啟：

除此外 SQL 2016+ 新增了一個 View sys.dm_exec_query_statistics_xml 也可用來取得執行中查詢的執行計劃
SQL2019 新加 ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON 方式可保存最近的執行計劃。參考：sys.dm_exec_query_plan_stats
用 Profiler 追蹤也可以取得執行計劃，但對效能影響較大，很少會這麼做
.NET SqlCommand.Commtext = "SELECT * FROM dbo.Orders WHERE CustomerID = @c" 寫法也會產生執行計劃快取，快取 Key 由查詢語法文字雜湊產生。所以 SELECT * FROM dbo.Orders WHERE CustomerID = @c、SELECT * FROM dbo.Orders WHERE CustomerID = @c (結尾多一個空白)、SELECT * FROM Orders WHERE CustomerID = @c (拿掉dbo.)、select * from dbo.Orders where CustomerID = @c (大小寫不同)會產生四份執行計劃快取。

注意：.NET SqlParameter 未指定長度可能導致多份執行計劃

cmd.CommandText = "SELECT * FROM dbo.Orders WHERE CustomerID = @c";
cmd.Parameters.Add("@c", SqlDbType.NVarChar).Value = TextBox.Value;
--會轉成如下指令，由 TextBox.Value 決定 nvarchar(n)
/*
exec sp_executesql N'SELECT * FROM Orders WHERE CustomerID = @c',
                   N'@c nvarchar(5)',@c=N'ALFKI'
*/
--字串長度不同，產生的 SQL 語法 nvarchar(n) 的 n 不同，會產生多份執行計劃
--若要避免請指定長度
cmd.Parameters.Add("@c", SqlDbType.NVarChar, 5).Value = TextBox.Value;

不同 DEFAULT_SCHEMA 的使用者不共用執行計劃快取，下圖為 DEFAULT_SCHEMA 為 dbo 或 Schema2 使用者查詢 SELECT * FROM Orders WHERE ... 留下的快取，user_id = 1 for dbo、user_id = 5 for Schema2。若指定 Schema SELECT * FROM dbo.Orders WHERE ...，則 user_id = -2 多使用者可共用執行計劃快取，這是表格、View、UDF 名稱一律加上 Schema 的好處(例：dbo.TableName，但 SP 例外，理由是 SP 會以自己所屬 Schema 為準，不隨使用者改變)，如果你的 DB 有區分 Schema，而非共用 dbo 的話。
Auto-Parameterisation - SQL 將常數自動轉成參數的行為，有兩種模式，Simple、Forced，由資料庫設定決定，預設為 Simple，並只有在 SQL 語句複雜度很低時才啟用(只會產生一種執行計劃)，故不會因 Parameter Sniffing 出問題。Forced 模式則會將所有常數參數化，例如：
```
ALTER DATABASE Northwind SET PARAMETERIZATION FORCED
DBCC FREEPROCCACHE
go
SELECT * FROM Orders WHERE OrderDate > '20000101'
go
SELECT * FROM Orders WHERE OrderDate > '19970101'
go    
```
在查詢計劃 SQL 語法會轉成 select * from Orders where OrderDate > @0，兩次共用同一個查詢計劃；若設為 Simple 模式，SQL 發現有兩種執行計劃(前者適合 Index Seek + Key Lookup，後者適合 Clusterd Index Scan)，則會停用自動參數化。
在 SSMS 模擬應用程式執行 SQL 動作，SET ARITHABORT OFF 之外的小技巧：
1. EXECUTE AS USER = 'appuser' 以應用程式使用的帳號執行，結束時 REVERT 復原
2. 用以下指令查應用程式送出的 SQL 指令
```
SELECT '<' + est.text + '>'
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
WHERE  est.text LIKE '%some significant SQL text here%'    
```
1. Retain CR/LF on copy or save <-- 存檔保留原始換行符號
2. 若沒有 Server 管理權限，ALTER TRACE 可授權追蹤、VIEW SERVER (PERFORMANCE) STATE 可查詢 sys.dm_exec_query_stats 及 sys.dm_exec_sql_text

進階技巧，導引產生想到的執行計劃(Plan Guide)，以下例子指定對 OrderDate Index Seek：

DBCC FREEPROCCACHE
go
EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', @orderdate = '19960101'
go
EXEC sp_create_plan_guide
     @name = N'MyGuide',
     @stmt = N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
     @type = N'SQL',
     @module_or_batch = NULL,
     @params = N'@orderdate datetime',
     @hints =  N'OPTION (TABLE HINT (dbo.Orders , INDEX (OrderDate)))'
go
EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', @orderdate = '19980101'
go
EXEC sp_control_plan_guide N'DROP', N'MyGuide'

進階技巧，若 Parameter Sniffing 會產生好的或壞的執行計劃，先用特定參數執行產生好的執行計劃，用 sp_create_plan_guide_from_handle 將其標註為建議使用的版本。
SQL 2016+，可啟用 Query Store 長期保存執行計劃及其統計資訊，以利事後分析應用。

不得不說，Erland 的文章滿滿乾貨，但也乾到不配溫開水難以吞服，最後附上我的精簡版精華結束這回合：

遇到網站跑 SP/SQL 很慢，移到 SSMS 飛快情況，起因為 Parameter Sniffing 會產生多個版本的執行計劃，有些執行計劃遇到特定參數值效能奇差。
SSMS 因預設 ARITHABORT ON，會另外產生執行計劃，避開不良的執行計劃，感覺解決了問題。 ARITHABORT 本身對查詢並無作用響，將應用程式也設成 ARITHABORT ON 並不能解決問題。
sp_recompile <sp_name> 會強制清空執行計劃快取，有機會解決問題
使用 SSMS、dm_exec_text_query_plan 等方式取得好、壞兩種執行計劃對照分析，透過一些技巧避免產生效率低落的執行計劃
可用技巧：OPTION (RECOMPILE)、增刪索引、加上索引提示、加上 OPTIMIZE 提示、將外界傳入參數轉成區域變數再使用可避免 Parameter Sniffing

Comments

# 2023-11-11 09:16 AM by Carson

重新賦值好像也可以解決到問題， sp 收到參數后， declare 一個新參數放進去sp 跑

# 2023-11-11 09:18 AM by Jeffrey

to Carson, 對，就是文末提到的 "將外界傳入參數轉成區域變數再使用" 技巧，手邊有案例是用這招解決。

# 2023-11-12 02:59 PM by River

黑大，類似問題在新版本的SQL Server 應該會陸續改進，可以參考一下：https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver16 從SQL 2017 以後的智慧查詢越來越強悍，希望靠人工介入調教的機會要變少，畢竟有經驗的DBA難找說~

Comments

# 2023-11-11 09:16 AM by Carson

# 2023-11-11 09:18 AM by Jeffrey

# 2023-11-12 02:59 PM by River

Post a comment