德瑞克：SQL Server 學習筆記: [SQL Server] Parameter Sniffing: sp

延續前文：[SQL Server] Parameter Sniffing: Pros and Cons, 參數探測

遇到 Stored Procedure 執行速度忽快忽慢，該怎麼辦?
除了，重新啟動 SQL Server，還能做什麼？

依據，是否要 Reuse Execution Plan，可以分成 2 類來討論：

手動執行 Recompile、停用 Reuse Execution Plan 功能。
有 4 種方式來達成。

A. Manual recompile: 手動執行 Recompile

01. sp_recompile: stored procedure, trigger, or user-defined function

sp_recompile: Recompile a particular stored procedure
就先由 Plan Cache 移除該 stored procedure 的 Execution Plan
類似於 DBCC FREEPROCCACHE 由 Plan Cache 中移除指定的 Execution Plan

02. sp_recompile: table or view

sp_recompile: Recompile a particular table
要等到下次執行 stored procedure，才將 Recompile 此 stored procedure。
使用到此 Table 的各項 SQL 物件：stored procedure, trigger, or user-defined function，都將 Recompile 。
與 sp_recompile: stored procedure, trigger, or user-defined function
比較起來，不先移除該 stored procedure 的 Execution Plan。

03. WITH RECOMPILE Option with EXECUTE statement

執行 stored procedure，搭配 WITH RECOMPILE

不方便。還需要帶入 User Parameter。

執行當下 Recompile 此 stored procedure。

B. Disable Reuse Execution Plan: 停用 Reuse Execution Plan 功能

04. WITH RECOMPILE Option with CREATE PROCEDURE

WITH RECOMPILE: recompile the stored procedures every time
每次都執行 Recompile SQL 陳述式，並且執行 Parameter Sniffing，每次都產生可能更有效率的 Execution Plan。

附註

為何會說：... 可能更有效率的 Execution Plan ?

這是因為，假若 Statistics 統計值不正確，也可能會影響到 Query Optimizer 所判斷要使用的 Index。

Lab 1: sp_recompile: Recompile a particular stored procedure

sp_recompile: stored procedure, trigger, or user-defined function

先由 Plan Cache 移除該 stored procedure 的 Execution Plan。
類似於 DBCC FREEPROCCACHE 由 Plan Cache 中移除指定的 Execution Plan

Good Execution Plan

StateProvinceID=32, return: 1 row,

Index Seek: [IX_Address_StateProvinceID]

StateProvinceID=9, return: 4564 rows,

Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

StateProvinceID=41, return: 1 row,

Index Seek: [IX_Address_StateProvinceID]

01. 第 1 次執行 StateProvinceID = 32，並執行 10 次

系統 compile 此 Execution Plan，使用 @StateProvinceID= 32。

-- 01_Run 10 times_First time use StateProvinceID = 32

02. 觀察效能 STATISTICS 統計資訊: CPU, Disk IO

Good Execution Plan
return: 1 row, Index Seek: [IX_Address_StateProvinceID]
last_logical_reads: 6
[ExecutionCount]: 10
[PlanRecompile]: 1

-- 02_Get last performance statistics for cached query

03. sp_recompile Recompile the particular stored procedure

對 stored procedure 執行 sp_recompile。
Object 'dbo.up_Parameter_Sniffing' was successfully marked for recompilation.

-- 03_sp_recompile Recompile the particular stored procedure

04. Have removed the Execution Plan of the particular Stored Procedure from the plan cache.

已經移除此 Stored Procedure 的 Execution Plan.

-- 04_Have removed the Execution Plan from the plan cache

05. 執行 StateProvinceID = 9

Plan cache 已經沒有 up_Parameter_Sniffing 的 Execution Plan。
系統 compile 此 Execution Plan，使用 @StateProvinceID= 9。

-- 11_2nd Changing Parameter Values StateProvinceID = 9

06. 觀察效能 STATISTICS 統計資訊: CPU, Disk IO

Good Execution Plan
return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
Efficient execution plan
Suggestion: Missing index
Last_logical_reads: 218
[ExecutionCount]: 1
[PlanRecompile]: 1

-- 12_Get last performance statistics for cached query

Lab 2: sp_recompile: Recompile a particular table

01. sp_recompile: Recompile a particular table

sp_recompile: Recompile 指定的 table
如果物件是 table 或 view，要等到下次執行 stored procedure，才將 Recompile 此 stored procedure。
使用到此 Table 的各項 SQL 物件：stored procedure, trigger, or user-defined function，都將 Recompile 。

-- 21_sp_recompile Recompile the table

02. 觀察效能 STATISTICS 統計資訊: CPU, Disk IO

該 stored procedure 的 Execution Plan 仍然存在。
要等到下次執行 stored procedure，才將 Recompile 此 stored procedure。

-- 22_Get last performance statistics for cached query

03. 執行 StateProvinceID = 32，並執行 5 次

系統 compile 此 Execution Plan，使用 @StateProvinceID= 32。
要等到下次執行 stored procedure，才將 Recompile 此 stored procedure。

-- 31_Run 5 times

04. 觀察效能 STATISTICS 統計資訊: CPU, Disk IO

Good Execution Plan
return: 1 row, Index Seek: [IX_Address_StateProvinceID]
last_logical_reads: 6
[ExecutionCount]: 6
[PlanRecompile]: 2

先前基底是 [ExecutionCount]: 1 與 [PlanRecompile]: 1。
已知 sp_recompile: Recompile 指定的 table，等到執行 stored procedure時，才將 Recompile 此 stored procedure。

再度觀察，已經變更： [ExecutionCount]: 6 與 [PlanRecompile]: 2。

-- 32_Get last performance statistics for cached query

Remove All Cache

-- 41_Remove_Cache

Lab 3: WITH RECOMPILE Option with EXECUTE statement

01. WITH RECOMPILE Option with EXECUTE statement。執行 StateProvinceID = 32。

執行 stored procedure，搭配 WITH RECOMPILE

不方便。還需要帶入 User Parameter。

執行當下 Recompile 此 stored procedure

觀察 Execution Plan

Good Execution Plan
return: 1 row, Index Seek: [IX_Address_StateProvinceID]

-- 42_WITH RECOMPILE Option with EXECUTE statement

02. 觀察效能 STATISTICS 統計資訊: CPU, Disk IO

Without the Execution Plan
沒有存放 Execution Plan

-- 43_Without the Execution Plan

03. WITH RECOMPILE Option with EXECUTE statement。執行 StateProvinceID = 9。

執行 stored procedure，搭配 WITH RECOMPILE

不方便。還需要帶入 User Parameter。

執行當下 Recompile 此 stored procedure

觀察 Execution Plan

Good Execution Plan
return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

-- 44_WITH RECOMPILE Option with EXECUTE statement

04. 觀察效能 STATISTICS 統計資訊: CPU, Disk IO

Without the Execution Plan
沒有存放 Execution Plan

-- 45_Without the Execution Plan

05. 刻意不輸入 user parameter，發生執行錯誤：

Error: only WITH RECOMPILE but without user Parameter
Msg 201, Level 16, State 4, Procedure dbo.up_Parameter_Sniffing, Line 0 [Batch Start Line 151]
Procedure or function 'up_Parameter_Sniffing' expects parameter '@StateProvinceID', which was not supplied.

-- 46_Error WITH RECOMPILE but without user Parameter