搜尋本站文章

2018-03-04

[SQL Server] Parameter sniffing occurs with Multi-Statement Table-Valued Function


遇到 MSTVF (multi-statement table valued functions, 多重陳述式資料表值函式) 執行效能 差,忽快忽慢,該怎麼辦?

若嘗試改寫為 Stored Procedure 或  Ad Hoc Query 等,發現其效能比 MSTVF 還要好。

這可能是受到 Parameter Sniffing 的影響。


若是直接觀察 MSTVF 的 Execution Plan

  • 僅能看到 Table Scan: 由 MSTVF 掃描全部的資料列。
  • 無法得知 MSTVF 內部 SQL script 的 Execution Plan。這將難以執行 Performance Tuning。

改用 DMV: dm_exec_query_plan 等來觀察,可以看到有 2 筆 與  MSTVF 相關的 Execution Plan。

  • 1 份 是與先前相同,可以看到 Table Scan: 由 MSTVF 掃描全部的資料列。
  • 另 1 份,是 MSTVF 內部 SQL Script 的 Execution Plan。可以此 Execution Plan 對 MSTVF 執行 Performance Tuning。




使用以下方式對 MSTVF 執行 Recompile ,重新編譯為 有效率 的 Execution Plan。
  • sp_recompile: Recompile the particular MSTVF.
  • sp_recompile: Recompile a particular table
  • OPTION (RECOMPILE)
或是

  • 如果 MSTVF 內只包含單一 SQL Query,請轉換為 Inline TVF。
  • 如果 包含多句 SQL Query,請考慮使用 in Memory-Optimized tables 或 temporary tables 來存放中繼結果。





Compare Reuse Good and Bad Execution Plan


以 StateProvinceID = 32 的 Good Execution Plan 為例:
  • 本該用 Index Seek: [IX_Address_StateProvinceID] 卻誤用為 Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]。

  • Logical Reads: 15,卻因故誤用,耗用到 439,差距 29.27 倍。
  • CPU Time(sec): 0.000771000,卻因故誤用,耗用到 0.002902000,差距 3.76 倍。



Parameter sniffing occurs with Multi-Statement Table-Valued Functions(MSTVF)


01. 建立 multi-statement table valued functions: ufn_GetCity_ParameterSniffing

-- figure 01_CREATE_FUNCTION_MSTVF_ufn_GetCity_ParameterSniffing




02. 在 Object Explorer ,觀察 已建立的 Table-valued Functions

-- figure 02_SSMS_Object_Explorer





03. 第 1 次,執行此 MSTVF,輸入 StateProvinceID = 9

觀察 Actual Execution Plan
  • 僅能看到 Table Scan: 由 MSTVF 掃描全部的資料列。
  • 無法得知 MSTVF 內部 SQL script 的 Execution Plan。這將難以執行 Performance Tuning。

-- figure 11_ufn_GetCity_ParameterSniffing_StateProvinceID_9



04. 使用 Plan Explorer 觀察

-- figure 12_Plan_Explorer





05. 改用 DMV: dm_exec_query_plan 等來觀察:

可以看到有 2 筆 與  MSTVF 相關的 Execution Plan。

  • 1 份 是與先前相同,可以看到 Table Scan: 由 MSTVF 掃描全部的資料列。
  • 另 1 份,是 MSTVF 內部 SQL Script 的 Execution Plan。可以此 Execution Plan 對 MSTVF 執行 Performance Tuning。

-- figure 13_MSTVF has 2 execution plans





06. 觀察 Performance statistics:

  • @StateProvinceID = 9
  • Good execution plan 
  • Return 4,564 row
  • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • LastCPUTime(sec): 0.016935000 + 0.012026000 = 0.028961000
  • LastLogicalReads: 5695 + 5666 = 11,361
  • EstimatedNumberofRows: 100
  • LasRows: 4564
  • ExecutionCount: 10


-- figure 14_觀察 Performance statistics




07. 由 DMV: dm_exec_query_plan 等,觀察 Execution Plan。

  • 看到 Table Scan: 由 MSTVF 掃描全部的資料列。

-- figure 15_Execution_Plan




08. 由 DMV: dm_exec_query_plan 等,觀察 Execution Plan。

  • 是 MSTVF 內部 SQL Script 的 Execution Plan。可以此 Execution Plan 對 MSTVF 執行 Performance Tuning。
  • Good execution plan 
  • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

-- figure 16_Execution_Plan_SQL




09. 使用 Plan Explorer 觀察

-- figure 17_Plan Explorer





10. 再度執行此 MSTVF,但更換輸入的參數值 StateProvinceID = 32

觀察 Actual Execution Plan

  • 僅能看到 Table Scan: 由 MSTVF 掃描全部的資料列。
  • 無法得知 MSTVF 內部 SQL script 的 Execution Plan。這將難以執行 Performance Tuning。

-- figure 21_ufn_GetCity_ParameterSniffing_StateProvinceID_32




11. 使用 Plan Explorer 觀察

-- figure 22_Plan_Explorer




12. 改用 DMV: dm_exec_query_plan 等來觀察:


可以看到有 2 筆 與  MSTVF 相關的 Execution Plan。

  • 1 份 是與先前相同,可以看到 Table Scan: 由 MSTVF 掃描全部的資料列。
  • 另 1 份,是 MSTVF 內部 SQL Script 的 Execution Plan。可以此 Execution Plan 對 MSTVF 執行 Performance Tuning。


-- figure 23_MSTVF has 2 execution plans





13. 觀察 Performance statistics:


  • @StateProvinceID = 32
  • Reuse the execution plan, however, it's a bad execution plan.
  • Return 1 row
  • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • LastCPUTime(sec): 0.001217000 + 0.001685000 = 0.002902000
  • LastLogicalReads: 218 + 221 = 439
  • EstimatedNumberofRows: 100
  • LasRows: 1
  • ExecutionCount: 10


-- figure 24_Performance statistics





14. 由 DMV: dm_exec_query_plan 等,觀察 Execution Plan。

  • 是 MSTVF 內部 SQL Script 的 Execution Plan。可以此 Execution Plan 對 MSTVF 執行 Performance Tuning。
  • Reuse the execution plan, however, it's a bad execution plan.
  • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

-- figure 25_Reuse the execution plan, however, it's a bad execution plan.




15. 由 DMV: dm_exec_query_plan 等,觀察 Execution Plan。

  • 看到 Table Scan: 由 MSTVF 掃描全部的資料列。

-- figure 26_Execution_Plan





16. 使用 Plan Explorer 觀察


-- figure 27_Plan Explorer





17. 移除 Plan Cache, clean buffers



18.  第 1 次,執行此 MSTVF,輸入 StateProvinceID = 32

觀察 Actual Execution Plan

  • 僅能看到 Table Scan: 由 MSTVF 掃描全部的資料列。
  • 無法得知 MSTVF 內部 SQL script 的 Execution Plan。這將難以執行 Performance Tuning。


-- figure 31_1st_ufn_GetCity_ParameterSniffing_StateProvinceID_32




19. 使用 Plan Explorer 觀察

-- figure 32_Plan_Explorer





20. 改用 DMV: dm_exec_query_plan 等來觀察:

可以看到有 2 筆 與  MSTVF 相關的 Execution Plan。

  • 1 份 是與先前相同,可以看到 Table Scan: 由 MSTVF 掃描全部的資料列。
  • 另 1 份,是 MSTVF 內部 SQL Script 的 Execution Plan。可以此 Execution Plan 對 MSTVF 執行 Performance Tuning。

-- figure 33_MSTVF has 2 execution plans





21. 觀察 Performance statistics:

  • @StateProvinceID = 32
  • Good execution plan 
  • Return 1 row
  • Index Seek: [IX_Address_StateProvinceID] 
  • LastCPUTime(sec): 0.000574000 + 0.000197000 = 0.000771000
  • LastLogicalReads: 9 + 6 = 15
  • EstimatedNumberofRows: 100
  • LasRows: 1
  • ExecutionCount: 10

-- figure 34_觀察 Performance statistics





22. 由 DMV: dm_exec_query_plan 等,觀察 Execution Plan。

  • 看到 Table Scan: 由 MSTVF 掃描全部的資料列。

-- figure 35_Execution_Plan





23. 由 DMV: dm_exec_query_plan 等,觀察 Execution Plan。

  • 是 MSTVF 內部 SQL Script 的 Execution Plan。可以此 Execution Plan 對 MSTVF 執行 Performance Tuning。
  • Good execution plan
  • Index Seek: [IX_Address_StateProvinceID]

-- figure 36_Execution_Plan




24. 使用 Plan Explorer 觀察


-- figure 37_Plan Explorer







Recompile Execution Plan for MSTVF


使用以下方式對 MSTVF 執行 Recompile ,重新編譯 為有效率的 Execution Plan。

  • sp_recompile: Recompile the particular MSTVF.
  • sp_recompile: Recompile a particular table
  • OPTION (RECOMPILE)


01. 使用 sp_recompile: Recompile a particular table

-- figure 41_sp_recompile_Recompile the table




02. 使用 sp_recompile: Recompile the particular MSTVF.

-- figure 42_Recompile the MSTVF_Multi-Statement Table-Valued Function




03. 使用 OPTION (RECOMPILE)

-- figure 43_Use OPTION (RECOMPILE)







Sample Code

20180304_Parameter sniffing occurs with MSTVF
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




References

CREATE FUNCTION (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-function-transact-sql

交錯執行多重陳述式資料表值函式
Interleaved execution for multi-statement table valued functions
https://docs.microsoft.com/zh-tw/sql/relational-databases/performance/adaptive-query-processing

[SQL Server] Parameter Sniffing: Pros and Cons, 參數探測
http://sharedderrick.blogspot.tw/2017/12/sql-server-parameter-sniffing-pros-and.html

[SQL Server] Parameter Sniffing: sp_recompile, WITH RECOMPILE
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing.html

沒有留言:

張貼留言