一個常見問題: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
  1. List_orders_1 日期寫成常數,SQL 分析資料分佈都介於 1968-1998,理論上沒有半筆,但基於參考來源是統計數據非實際資料,不確定一定沒資料,Estimated Number of Rows Per Execution 取 1
  2. List_orders_2 日期為參數,但 Optimisation 過程會用 20000101 分析,預測筆數也是 1。這個過程稱為 Parameter Sniffing (參數嗅探)
  3. List_orders_3 執行時,SQL 不會分析流程邏輯,不知區域變數 @fromdate_copy 來自 @fromdate,假設日期未知,猜 30%,830 * 0.3 = 249
  4. 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
  1. 新版 SSMS 執行計劃流程會直接標示預測及實際筆數,但 Popup 視窗仍有 Number of Executions 跟 Estimated Number of Execution 等重要資訊可參考
  2. 每次重建執行計劃很耗效能,對動轍一分鐘起跳的 OLAP 複雜查詢無所謂且有個別最佳化效果;但在 OLTP 會把 SP 執行計劃存入 Cache 重複以提升效率,直到以下狀況才清除:
    1. Buffer Cache 空間滿了(執行計劃與資料表資料共用),SQL 會清掉很久沒使用的
    2. 執行 ALTER PROCEDURE
    3. 執行 sp_recompile
    4. 執行 DBCC FREEPROCCACHE 清空所有執行計劃快取
    5. SQL 重啟
    6. sp_configure 修改某些 SQL 伺服器參數
  3. 清除快取後,下回執行 SP 會重做 Parameter Sniffing 產生執行計劃 (執行計劃可能與之前不同)
  4. 有些事件會導致 SP 的部分指令重新編譯
    1. 相關資料表結構異動
    2. 索引異動(ALTER INDEX/DBCC DBREINDEX)
    3. 更新統計(SQL自動或CREATE STATISTICS/ UPDATE STATISTICS) (不一定觸發重新編譯)
    4. 對相關資料表執行 sp_recompile
  5. 建立何種執行計劃由第一次執行的 Parameter Sniffing 決定,故很有可能該執行計劃不適合後續的其他查詢,這是實務上出問題的常見原因。
  6. 這點很重要: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
*/
  1. 以下選項組合可想成 Cache Key:ANSI_PADDING, Parallel Plan, CONCAT_NUL_YIELDS_NULL, ANSI_WARNINGS, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_NULL_DFLT_ON 及 ARITHABORT,任一個值不同,Cache Key 不同,對映一份專屬執行計劃
  2. .NET 跟 SSMS 的預設選項只差在 ARITHABORT,這就是為什麼二者會因使用不同執行計劃,並出現截然不同的查詢速度,而在 SSMS 關閉 ARITHABORT 或加上 SET ARITHABORT OFF 可讓 SSMS 重現 .NET 程式的查詢速度:
  3. 在 .NET 跑查詢時也補上 SET ARITHABORT ON 可以避開應用程式跟 SSMS 跑相同查詢效能天差地別的狀況,但並無法解決因執行計劃不佳造成查詢爆慢問題。

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

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


Comments

# by GregYu

之前碰過這個問題, 最後靠著檢視 SP 內部的 SQL 指令, 在某個 Table 增加 Index 解決這個問題 Erland Sommarskog 的文章當時有研究過, 我小小的心得是: 造成執行速度會有快慢的差異, 固然是因為 [執行計畫] 不同所造成, 可之所以會採用相異的 [執行計畫], 除了 [ARITHABORT] 這個參數之外, 很多時候正是因為 Index 的限制, 造成在不同情況下,有迥異的評估結果, 最終帶來效能上的落差

# by Danny

個人也遇到類似被執行計畫坑了的問題,同一句SQL,就是查詢的日期範圍有差別,執行速度天差地別。查詢9月份數據,大概有1萬筆數據,秒出。查詢10月份數據,只有區區1百多筆,但花時1分多鐘。靈異的是查詢範圍涵蓋9月份最後一天的話也是秒出⋯ 搞了好久才發現兩個用了不同的執行計畫,慢的那個不知道為啥Table scan了上百億的資料,導致明明數據很少卻花最多時間查詢導致前端程序超時。 最後也是加Index解決問題

# by Darren

超級感謝 今天正好遇到!! asp.net 執行一個無參數的proc, 一直逾時 但在SSMS下約10秒還是可以跑出來 以前也知道加index或重建indez有時可以解決 但這次重建了index也無效 最後也是在.net端加了SET ARITHABORT ON搞定

# by Jerry

在此非常感謝黑大的無私分享!!! 開發經驗那麼久第一次碰到這個奇特的問題,還以為七月ghost event特別多? 原來是我才疏學淺呀,真的是增廣見聞了。 事情原委大概如下: 開發一個內部系統一個功能,統計使用者在所屬群組內對於其他同組人員觀看文章的未讀數。 我用MS-SQL的資料表函式來處理資料,前陣子最高權限的管理者(可以看到最多的群組及群組人員的未讀數)反應進入頁面超級慢,甚至慢到畫面出不來,Debug經查查詢資料表函式到timeout,但是權限較小的使用者則不會有跑到timeout的問題。 Non clustered index加了後只快了一些,查詢統計不到萬筆的資料要一二十秒的時間覺得很怪,反覆測試發現AP端去查詢資料時會Declare一個變數,再把變數傳給資料表函式,於是在SSMS端測試同樣Declare一個變數餵給資料表函式,跟直接Select * from TableFunction ('XXX') 直接帶入變數,居然回應時間天差地遠,後來同事找到這篇文章: https://www.sqlshack.com/impact-of-sql-variables-on-performance/ 於是改用該文章末利用sp_executesql的方式來執行SQL函式語法,並存成Stored Procedure讓AP端呼叫,經多次測試API的反應時間平均都在一秒左右拿到資料。原以為這懸案就是落幕,沒想到過一個多禮拜,管理者又反應網頁畫面出不來,經查又是同個問題查詢SQL Server到timeout,對此感到很莫名,怎麼會有時好時壞的情況發生?要嘛第一次改法就不行了,怎麼會好了幾天又不行了?研究了好一會兒,發現到在SSMS呼叫SP的時間就正常,在AP端隨著權限越高者,呼叫更多次資料表函式,查詢時間也就更久,最高權限的人竟超過5分鐘了還是timeout沒查到資料,於是在AP端加上SET ARITHABORT ON;就解決了,不到一秒的回應時間真是令人驚嘆@@

Post a comment