「網站執行 Stored Procedure 爆慢，移到 SSMS 跑卻飛快」問題之徹底研究

一個常見問題：SQL 上某個 Stored Procedure (以下簡稱 SP)，從網站應用程式執行超慢，甚至等到逾時報錯；將同樣的 SP 及呼叫參數拿到 SSMS (SQL Server Management Studio) 執行卻瞬間完成；或是 SP 慢到無法忍受，將其中語法邏輯 Copy 出來單獨執行卻沒半點問題。

我最近遇到的案例是 SP 查詢某類別資料時超過 30 秒，其他類別都是一秒完成；將 SP 查詢移到 SSMS 測試，不管任何類別都是一秒完成。問題關鍵很神奇也很簡單，差在一個旗標設定 SET ARITHABORT ON，原因是旗標不同導致 ASP.NET 與 SSMS 使用不同的執行計劃，而 ASP.NET 採用的執行計劃遇上問題類別明顯有問題，清除並重新產生執行計劃通常就能解決。
憑著印象找到 Rico 這篇 SSMS執行SP就是比.NET 快很多? - RiCosNote，停用 SSMS SET ARITHABORT ON 成功重現查詢超過 30 秒快速破案，特此感謝。

問題雖解，對我來說具體原因仍然是謎，要如何防範心中也沒譜，便再花了點時深入研究，小有心得，在此分享。

強力推薦這篇 - Slow in the Application, Fast in SSMS? (sommarskog.se)，SQL Server MVP Erland Sommarskog 一篇 2011 年的老文章(網頁風格充滿年代感)，但作者到今年都還有更新，範圍甚至涵蓋 SQL 2022，故不用擔心資訊過時。

文章詳細解釋 SP 如何編譯、何謂 Parameter Sniffing、SQL 的執行計劃快取行為，有助理解「SP 在 SSMS 跑得比較快」這件事是怎麼發生的。

由於文章宇宙無敵長，內容既深又廣，得寫成筆記幫助吸收，以下是我摘要的重點。

1. 查詢 Plan Cache 需要 VIEW SERVER STATE 權限 (SQL 2022 則是 VIEW SERVER PERFORMANCE STATE)
2. SP、Scalar UDF (SQL 2019+)、Multi-Step Table Value Function、Trigger 擁有自己專屬的執行計劃
3. 在 SP 呼叫另一個 SP，二者的執行計劃是獨立的，在 SP 動態組裝 SQL 用 sp_executesql 執行，則不會包含在 SP 的執行計劃裡
4. SP 直到第一次執才會建立執行計劃，每次執行 SQL 依資料分佈統計預測最佳執行方式(這個程序稱為 Optimisation)
5. List_orders_3 的執行計劃會跟前兩者不同，不會查索引，而是使用 Clustered Index Scan (相當於 Table Scan)，原因其預測 Estimated Number of Rows Per Execution 是 249 (前二個是 1)，筆數多 Table Scan 比 Index Seek + Key Lookup 有效率。(北風資料庫 Orders 資料表只有 830 筆)

CREATE PROCEDURE List_orders_1 AS
   SELECT * FROM Orders WHERE OrderDate > '20000101'
go
CREATE PROCEDURE List_orders_2 @fromdate datetime AS
   SELECT * FROM Orders WHERE OrderDate > @fromdate
go
CREATE PROCEDURE List_orders_3 @fromdate datetime AS
   DECLARE @fromdate_copy datetime
   SELECT @fromdate_copy = @fromdate
   SELECT * FROM Orders WHERE OrderDate > @fromdate_copy
go

6. List_orders_1 日期寫成常數，SQL 分析資料分佈都介於 1968-1998，理論上沒有半筆，但基於參考來源是統計數據非實際資料，不確定一定沒資料，Estimated Number of Rows Per Execution 取 1
7. List_orders_2 日期為參數，但 Optimisation 過程會用 20000101 分析，預測筆數也是 1。這個過程稱為 Parameter Sniffing (參數嗅探)
8. List_orders_3 執行時，SQL 不會分析流程邏輯，不知區域變數 @fromdate_copy 來自 @fromdate，假設日期未知，猜 30%，830 * 0.3 = 249
9. List_orders_4 與跟 List_orders_1、List_orders_2 執行計劃相同，理由是編譯時 @formdate 是用 NULL 預估無符合值，預測筆數取 1，但實際會傳回所有資料

CREATE PROCEDURE List_orders_4 @fromdate datetime = NULL AS
   IF @fromdate IS NULL
      SELECT @fromdate = '19900101'
   SELECT * FROM Orders WHERE OrderDate > @fromdate

10. 新版 SSMS 執行計劃流程會直接標示預測及實際筆數，但 Popup 視窗仍有 Number of Executions 跟 Estimated Number of Execution 等重要資訊可參考
11. 每次重建執行計劃很耗效能，對動轍一分鐘起跳的 OLAP 複雜查詢無所謂且有個別最佳化效果；但在 OLTP 會把 SP 執行計劃存入 Cache 重複以提升效率，直到以下狀況才清除：
    1. Buffer Cache 空間滿了(執行計劃與資料表資料共用)，SQL 會清掉很久沒使用的
    2. 執行 ALTER PROCEDURE
    3. 執行 sp_recompile
    4. 執行 DBCC FREEPROCCACHE 清空所有執行計劃快取
    5. SQL 重啟
    6. sp_configure 修改某些 SQL 伺服器參數
12. 清除快取後，下回執行 SP 會重做 Parameter Sniffing 產生執行計劃 (執行計劃可能與之前不同)
13. 有些事件會導致 SP 的部分指令重新編譯
    1. 相關資料表結構異動
    2. 索引異動(ALTER INDEX/DBCC DBREINDEX)
    3. 更新統計(SQL自動或CREATE STATISTICS/ UPDATE STATISTICS) (不一定觸發重新編譯)
    4. 對相關資料表執行 sp_recompile
14. 建立何種執行計劃由第一次執行的 Parameter Sniffing 決定，故很有可能該執行計劃不適合後續的其他查詢，這是實務上出問題的常見原因。
15. 這點很重要：SQL 會為不同 Attribute 或選項組合建立一份專屬執行計劃，以下例子中，不同 DATEFORMAT 各有一份執行計劃：

CREATE PROCEDURE List_orders_6 AS
   SELECT *
   FROM   Orders
   WHERE  OrderDate > '12/01/1998'
go
SET DATEFORMAT dmy
go
EXEC List_orders_6
go
SET DATEFORMAT mdy
go
EXEC List_orders_6
go
-- 用以下指令可觀察到兩個不同版本執行計劃
SELECT qs.plan_handle, a.attrlist
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
              FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
              WHERE  epa.is_cache_key = 1
              ORDER  BY epa.attribute
              FOR    XML PATH('')) AS a(attrlist)
WHERE  est.objectid = object_id ('dbo.List_orders_6')
  AND  est.dbid     = db_id('Northwind')
/*
plan_handle                     attrlist
------------------------------- -------------------------------------------------
0x0500070064EFCA5DB8A0A90500... compat_level=150   date_first=7   date_format=1
                                set_options=4347   user_id=1
0x0500070064EFCA5DB8A0A80500... compat_level=150   date_first=7   date_format=2
                                set_options=4347   user_id=1
*/

16. 以下選項組合可想成 Cache Key：ANSI_PADDING, Parallel Plan, CONCAT_NUL_YIELDS_NULL, ANSI_WARNINGS, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_NULL_DFLT_ON 及 ARITHABORT，任一個值不同，Cache Key 不同，對映一份專屬執行計劃
17. .NET 跟 SSMS 的預設選項只差在 ARITHABORT，這就是為什麼二者會因使用不同執行計劃，並出現截然不同的查詢速度，而在 SSMS 關閉 ARITHABORT 或加上 SET ARITHABORT OFF 可讓 SSMS 重現 .NET 程式的查詢速度：
18. 在 .NET 跑查詢時也補上 SET ARITHABORT ON 可以避開應用程式跟 SSMS 跑相同查詢效能天差地別的狀況，但並無法解決因執行計劃不佳造成查詢爆慢問題。

至於要怎麼解決執行計劃不適合造成效能問題，要從了解 Parameter Sniffing 下手，這又是另一個值得深入的大議題，留待下一篇討論。

Introduction to the execution plan cache issue on perfermance difference between application and SSMS.