德瑞克：SQL Server 學習筆記: [SQL Server] Compare "WITH RECOMPILE / sp

延續前文：[SQL Server] Parameter Sniffing: OPTION (RECOMPILE)

別再誤以為：只要使用 sp_recompile 或 WITH RECOMPILE 就可以解決 Parameter Sniffing 問題。

在 Stored Procedure 內使用 Local Variables 或 OPTIMIZE FOR UNKNOWN 時：

就算是 sp_recompile 或 WITH RECOMPILE 也無法解決 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用到效能差的 Bad Execution Plan!

若因使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用到效能差的 Bad Execution Plan。

可以採用以下方式來因應，例如：

使用 OPTION (RECOMPILE)

僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值。

移除使用 Local Variables
改用 Literal Values (常值)
改用 Dynamic SQL

許多朋友對以下問題，也是一知半解：

以 Stored Procedure 為例，"sp_recompile 或 WITH RECOMPILE" 與 "OPTION (RECOMPILE)" 兩者的差別在哪裡呢？

	Level	Description
sp_recompile or WITH RECOMPILE	Stored Procedure Level	Recompile 整個 Stored Procedure 但無法評估到 Local Variable 的值
OPTION (RECOMPILE)	Statement Level	僅 Recompile 指定的 SQL Statement 可使用到 Local Variable 的值

	Level	Description
sp_recompile or WITH RECOMPILE	Stored Procedure Level	Recompile whole stored procedure. Can't use the values of local variables.
OPTION (RECOMPILE)	Statement Level	Only statement-level recompilation, instead of the whole stored procedure. Use the values of any local variables.

1. Local Variable and "OPTION RECOMPILE" have used simultaneously

若因使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用到效能差的 Bad Execution Plan。

除了，移除使用 Local Variables，或是改用 Literal Values (常值)、Dynamic SQL 等，還有什麼方法呢？

可以使用 OPTION (RECOMPILE)

僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值。

而 sp_recompile 與 WITH RECOMPILE，是無法解除 "Local Variables / OPTIMIZE FOR UNKNOWN" 所帶來的副作用!

01. 建立 Stored Procedure: up_Parameter_Local_Variable_OPTION_RECOMPILE

使用 Local Variable
使用 OPTION (RECOMPILE) - 僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值

-- figure 01_Local Variable and OPTION RECOMPILE have used simultaneously

02. 設定 Input Parameter: @StateProvinceID= 32，執行此 Stored Procedure。

使用 Local Variable
使用 OPTION (RECOMPILE) - 僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值
return 1 row, Index Seek: [IX_Address_StateProvinceID]
Good execution plan

-- figure 11_StateProvinceID_32

03. 觀察由 Plan Cache 內所記錄的 Execution Plan

1st: 將 Input Parameter 指定給 Local Variable
2nd: 使用 OPTION (RECOMPILE) - 僅 Recompile 指定的 SQL Statement，可使用到 Local Variable 的值

-- figure 12_Plan_Cache_Execution_Plan

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

-- figure 13_ExecutionCount_PlanRecompile

05. 若僅使用 Local Variable。觀察效能 STATISTICS 統計資訊: CPU and Disk IO, etc.

-- figure 14_Only_Use_Local Variable

06. 比較 Only use Local Variable vs. Local Variable and "OPTION RECOMPILE"

-- figure 21_ExecutionCount_PlanRecompile

若 Only use Local Variable，將由原本 LastLogicalReads: 4 ，大幅增加耗用到 218，差距： 54.5 倍。
這皆因 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用了 Bad Execution Plan。

2. Local Variable and "sp_recompile" have used simultaneously: invalid, like used Local Variable

請留意：
sp_recompile 與 WITH RECOMPILE，是無法解除 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用到效能差的 Bad Execution Plan!

01. 對有使用 Local Variables 的 Stored Procedure，執行 sp_recompile。

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

若因 Local Variables 用到效能差的 Bad Execution Plan，則 sp_recompile 是無法解除的!

應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。

-- figure 31_Local Variable and sp_recompile

02. 若使用 Local Variable。觀察效能 STATISTICS 統計資訊: CPU and Disk IO, etc.

即便使用了 sp_recompile，卻仍是使用到效能差的 Bad Execution Plan。
應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。
由原本 LastLogicalReads: 4 ，大幅增加耗用到 218，差距： 54.5 倍。
這皆因 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用效能差的 Bad Execution Plan。

-- figure 32_StateProvinceID_32

3. Local Variable and "EXEC ... WITH RECOMPILE" have used simultaneously: invalid, like used Local Variable

若因 Local Variables 用到效能差的 Bad Execution Plan，則 WITH RECOMPILE 是無法解除的!

01. 為了測試目的，先執行:

-- Clearing all plans from the plan cache
DBCC FREEPROCCACHE;

02. 對有使用 Local Variables 的 Stored Procedure，執行 10 次 Stored Procedure，並使用 WITH RECOMPILE。

執行 stored procedure，搭配 WITH RECOMPILE
不方便。還需要帶入 User Parameter。
執行當下 Recompile 此 stored procedure

若因 Local Variables 用到效能差的 Bad Execution Plan，則 WITH RECOMPILE 是無法解除的!
應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。

-- figure 41_Local Variable and EXEC WITH RECOMPILE

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

Without the Execution Plan
沒有存放 Execution Plan

即便使用了 WITH RECOMPILE，卻仍是使用到效能差的 Bad Execution Plan。
應該使用 Index Seek，卻因 Local Variables 而誤用為 Index Scan。
由原本 Logical Reads: 4 ，大幅增加耗用到 218，差距： 54.5 倍。
這皆因 "Local Variables / OPTIMIZE FOR UNKNOWN" 使用效能差的 Bad Execution Plan。

-- figure 43_ExecutionCount_PlanRecompile

-- figure 44_Logical Reads: 218

4. Local Variable and "WITH RECOMPILE" have used simultaneously: invalid, like used Local Variable

若因 Local Variables 用到效能差的 Bad Execution Plan，則 WITH RECOMPILE 是無法解除的!

01. 為了測試目的，先執行:

-- Clearing all plans from the plan cache
DBCC FREEPROCCACHE;

02. 建立 Stored Procedure: up_Parameter_Local_Variable_WITH_RECOMPILE：