德瑞克：SQL Server 學習筆記: [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.