2017-12-29

[SQL Server] Parameter Sniffing: Pros and Cons, 參數探測


上網忽快忽慢？可能是受到來自駭客的網路攻擊。

但 Stored Procedure 執行速度 忽快忽慢？怎麼會這樣？
這可能是受到 Parameter Sniffing 的影響。

了解 Parameter Sniffing 功能，將有助於開發高效能 Stored Procedure，而 SQL Server: Parameter Sniffing 類似於 Oracle: Bind Variables。本文將先討論什麼是 Parameter Sniffing，以及其優缺點。後續文章中，將陸續介紹其解決方案與應用方式。


Parameter Sniffing (參數探測)

「Parameter Sniffing」
  • 是指 SQL Server 在 compile 編譯 或 recompile 重新編譯期間，執行「sniffs 探查」目前的參數值。
  • 然後將它傳遞給 Query Optimizer，以便可用來產生可能 更有效率 之 Execution Plan 的程序。

若每次執行 SQL 陳述式，都要重新 recompile SQL 陳述式，將額外耗損掉系統資源。為了節省所耗用的系統資源，SQL Server 採取 reuse 重複使用 Execution Plan 的方式。


在 編譯 或 重新編譯 期間，會探查下列批次類型的參數值：
  • Stored procedures
  • 透過 sp_executesql 提交的查詢
  • Prepared queries 準備查詢

Procedure流程

  • 第 1 次，執行 Stored procedures，Query Optimizer 使用 Parameter Sniffing 功能 探查，並建立適合的 Execution Plan。
  • 第2次，Reuse 同一份 Execution Plan。

Pro and Cons of Parameter Sniffing

Advantage
Disadvantage
  • Reuse Execution Plan, save overhead of recompilation.
  • 重複使用，省下 recompile 重新編譯 SQL 陳述式的系統負擔
  • Maybe Reuse bad Execution Plan
  • 因故 Reuse 到 不合適 的 Execution Plan，使得 效能 更糟，導致耗損 更多 的系統資源。
  • 也可能使得 Estimated Subtree Cost 不具備使用價值。


Summary





  • 使用 Parameter Sniffing 可節省 recompile  的系統資源。
  • 但也可能 reuse 到不適合的 Execution Plan，使得執行 效能 更糟，耗用更多的系統資源!
  • 甚至使得  Estimated Subtree Cost 不具備使用價值。


Compare Reuse Good and Bad Execution Plan

以 StateProvinceID=9 的 Good Execution Plan 為例:

  • Logical Reads: 218，但卻有可能因故誤用，而耗用到 9,141，差距 41.93 倍。
  • CPU Time(sec): 0.004337，但卻有可能因故誤用，而耗用到 0.018172，差距 4.19 倍。

以 StateProvinceID=32 的 Good Execution Plan 為例:
  • Logical Reads: 6，但卻有可能因故誤用，而耗用到 218，差距 36.33 倍。
  • CPU Time(sec): 0.000448，但卻有可能因故誤用，而耗用到 0.002387，差距 5.32 倍。


Lab 1: Parameter Sniffing: Index Seek

Reuse Good
 Reuse Bad
1st
@StateProvinceID = 32
  • return: 1 row, Index Seek
  • LastElapsedTime(sec): 0.070084000
  • LastCPUTime(sec)]: 0.000448000
  • last_logical_reads: 6
2nd
@StateProvinceID= 41
  • return: 1 row, Index Seek:
  • LastElapsedTime(sec): 0.088746000
  • LastCPUTime(sec): 0.000666000
  • Last_logical_reads: 7
3rd
@StateProvinceID= 9
  • return: 4564 row, Index Seek
  • Unexpected: without missing index
  • LastElapsedTime(sec): 0.170785000
  • LastCPUTime(sec): 0.018172000
  • Last_logical_reads: 9141


Lab 2: Parameter Sniffing: Index Scan
Reuse Good
Reuse Bad
1st
@StateProvinceID = 9
  • return: 4564 rows, Index Scan
  • LastElapsedTime(sec): 0.121807000
  • LastCPUTime(sec): 0.004337000
  • Last_logical_reads: 218
2nd
@StateProvinceID= 32
  • return: 1 row, Index Scan
  • LastElapsedTime(sec): 0.002388000
  • LastCPUTime(sec): 0.002387000
  • Last_logical_reads: 218



Parameter Sniffing: Pros and Cons


示範環境：SQL Server 2017

01: 建立 Stored Procedure: dbo.up_Parameter_Sniffing

  • Input Parameter 是 @StateProvinceID 參數值。
  • WHERE 條件式 使用 @StateProvinceID 參數值：由前端程式傳入，等到 compile (編譯) 或 recompile (重新編譯) 期間 ，將其傳遞給 Query Optimizer，藉此機會執行優化，產生有效率的 Execution Plan.
-- figure 01_Create_up_Parameter_Sniffing



02. 啟用 Enable both of the options: STATISTICS Disk IO, CPU

-- figure 02_Enable both of the options Disk IO, CPU





已知 Good Execution Plan

  • StateProvinceID=32, return: 1 row
    • Index Seek: [IX_Address_StateProvinceID]
    • last_logical_reads: 6
  • StateProvinceID=9, return: 4564 rows
    • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
    • Suggestion: Missing index
    • last_logical_reads: 218
  • StateProvinceID=41, return: 1 row
    • Index Seek: [IX_Address_StateProvinceID]
    • last_logical_reads: 7
觀察
  • StateProvinceID= 9 回傳資料筆數多達 4564 列，採用 Index Scan。



Lab1: Parameter Sniffing: Index Seek

First time use @StateProvinceID = 32


01. 第一次執行  dbo.up_Parameter_Sniffing，使用 @StateProvinceID = 32
  • 系統 compile 此 Execution Plan，使用 @StateProvinceID= 32。
-- figure 03_StateProvinceID_32_return_1 row



02. 觀察 Execution Plan
  • Parameterization 參數化: WHERE StateProvinceID= @StateProvinceID
-- figure 04_StateProvinceID_32_Execution_Plan



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

-- figure 05_Get last performance statistics for cached query



-- figure 06_Look_At_last performance statistics



Summary

@StateProvinceID = 32

  • Good Execution Plan
  • return: 1 row, Index Seek: [IX_Address_StateProvinceID]
  • Table 'Address'. Scan count 1, logical reads 4, physical reads 2,
  • LastElapsedTime(sec): 0.070084000
  • LastCPUTime(sec)]: 0.000448000
  • last_logical_reads: 6


Changing Parameter Values @StateProvinceID = 41


01. 執行  dbo.up_Parameter_Sniffing，使用 @StateProvinceID = 41

02. 觀察 Execution Plan
  • Parameterization 參數化: WHERE StateProvinceID= @StateProvinceID
  • @StateProvinceID = 32 與 41 ，都是使用 同一份 Execution Plan，減少 compile 所耗費的系統資源。

-- figure 11_StateProvinceID_41_Execution_Plan



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

-- figure 12_Look_At_last performance statistics



Summary

@StateProvinceID= 41

  • Good Execution Plan
  • return: 1 row, Index Seek: [IX_Address_StateProvinceID]
  • Table 'Address'. Scan count 1, logical reads 5, physical reads 2,
  • LastElapsedTime(sec): 0.088746000
  • LastCPUTime(sec): 0.000666000
  • Last_logical_reads: 7


Look at Execution Plan

01. 觀察 @StateProvinceID= 32 的 Execution Plan XML

-- figure 21_Show_Execution_Plan_XML




02. 觀察 ParameterList element
  • ParameterCompiledValue="(32)" 
  • ParameterRuntimeValue="(32)"
-- figure 22_Show Execution Plan XML_32



03. 由 SSMS 的 GUI 介面，觀察 Execution Plan: ParameterList
  • Parameter Compiled Value: (32)
  • Parameter Runtime Value: (32)
  • Estimated Subtree Cost: 0.0065704
-- figure 23_Look_At_Parameter_List_32



04. 觀察 ParameterList element
  • ParameterCompiledValue="(32)" 
  • ParameterRuntimeValue="(41)"
-- figure 24_Show Execution Plan XML_41



05. 由 SSMS 的 GUI 介面，觀察 Execution Plan: ParameterList
  • Parameter Compiled Value: (32)
  • Parameter Runtime Value: (41)
  • Estimated Subtree Cost: 0.0065704
-- figure 25_Look_At_Parameter_List_41



確認
  • Reuse 重複使用 同一份 Execution Plan，減少 compile 所耗費的系統資源。


Advantage of Parameter Sniffing
  • Reuse Execution Plan, save overhead of recompiling the SQL statement.
  • 重複使用，省下 recompile 重新編譯 SQL 陳述式的系統負擔


Side effect for Parameter Sniffing

 Changing Parameter Values @StateProvinceID = 9


已知 Good Execution Plan
  • StateProvinceID=32, return: 1 row
    • Index Seek: [IX_Address_StateProvinceID]
    • last_logical_reads: 6
  • StateProvinceID=9, return: 4564 rows
    • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
    • Suggestion: Missing index
    • last_logical_reads: 218

01. 改用 @StateProvinceID = 9 參數值

  • 依照先前 Stored Procedure: up_Parameter_Sniffing 範例，繼續沿用 @StateProvinceID = 32 的 Execution Plan，採用 Index Seek: [IX_Address_StateProvinceID]。
  • 而當 StateProvinceID = 9 時，Query Optimizer 會是選擇使用 Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]，並產生 Missing index 的建議。

但因為 Parameter Sniffing 功能：Reuse 同一份 Execution Plan，節省 recompile 重新編譯 SQL 陳述式的系統負擔。

使得 @StateProvinceID = 9，被強迫 reuse 不合適的 Index Seek: [IX_Address_StateProvinceID]，導致效能不佳。

Bad Execution Plan

  • @StateProvinceID= 9, return: 4564 rows
  • Index Seek: [IX_Address_StateProvinceID]
  • Unexpected: without missing index

-- figure 31_StateProvinceID_Bad Execution Plan




02. 觀察 ParameterList element
  • ParameterCompiledValue="(32)" 
  • ParameterRuntimeValue="(9)"

-- figure 32_Show Execution Plan XML_9




03. 由 SSMS 的 GUI 介面，觀察 Execution Plan: ParameterList
  • Parameter Compiled Value: (32)
  • Parameter Runtime Value: (9)
  • Estimated Subtree Cost: 0.0065704

請注意
  • Estimated Subtree Cost 仍是 0.0065704，這是 @StateProvinceID = 32 評估 的 Subtree Cost 。不是 @StateProvinceID = 9 的 Subtree Cost。 

-- figure 33_Look_At_Parameter_List_9




04. 由 SSMS 的 GUI 介面，觀察 Execution Plan:

  • Estimated Number of Rows: 1
  • Estimated Number of Rows to Read: 1
  • Number of Rows Read: 4564
  • Actual Number of Rows: 4564

使用了 不適合 的 Execution Plan ，沿用先前的評估值是 1 row，但實際卻是 4564 rows。

-- figure 34_Actual_Estimate_row




05. 點選 Operator 之間的連接線:

  • Actual Number of Rows: 4564
  • Number of Rows Read: 4564
  • Estimated Number of Rows: 1

-- figure 35_Actual_Estimate_row





06. 改用回 StateProvinceID=41，並與 @StateProvinceID=32 比較起來:

  • return: 1 row
  • Index Seek: [IX_Address_StateProvinceID]

這兩者(32 與 41)，適合 reuse 同一份 Execution Plan，節省 recompile 的系統負擔。
  • Estimated Number of Rows: 1
  • Estimated Number of Rows to Read: 1
  • Number of Rows Read: 1
  • Actual Number of Rows: 1

-- figure 36_Compare_Actual_Estimate




07. 點選 Operator 之間的連接線:

  • Actual Number of Rows: 1
  • Number of Rows Read: 1
  • Estimated Number of Rows: 1

-- figure 37_Compare_Actual_Estimate




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

-- figure 38_Look_At_last performance statistics



Summary

@StateProvinceID= 9

  • Bad Execution Plan
  • return: 4564 row, Index Seek: [IX_Address_StateProvinceID]
  • Unexpected: without missing index
  • Table 'Address'. Scan count 1, logical reads 9139, physical reads 2,
  • LastElapsedTime(sec): 0.170785000
  • LastCPUTime(sec): 0.018172000
  • Last_logical_reads: 9141

Disadvantage of Parameter Sniffing

  • Maybe Reuse bad Execution Plan.
  • 因故 Reuse 到 不合適 的 Execution Plan，使得 效能 更糟，導致耗損 更多 的系統資源。
  • 因為 Side effect for Parameter Sniffing，也使得 Estimated Subtree Cost 不具備使用價值。



Remove Cache

-- figure 41_Remove_Cache





Lab2: Parameter Sniffing: Index Scan

First time use @StateProvinceID = 9


01. 第一次執行  dbo.up_Parameter_Sniffing，使用 @StateProvinceID = 9
  • 系統 compile 此 Execution Plan，使用 @StateProvinceID= 9。

Good Execution Plan

  • @StateProvinceID= 9: return: 4564 rows
  • Index Scan: 
    • [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • Suggestion: Missing index


-- figure 42_StateProvinceID_9_Good_Execution_Plan




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

-- figure 43_Look_At_last performance statistics



Summary

@StateProvinceID = 9

  • Good Execution Plan
  • return: 4564 rows
  • Index Scan: 
    • [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • Suggestion: Missing index
  • Table 'Address'. Scan count 1, logical reads 216, physical reads 1, read-ahead reads 211,
  • LastElapsedTime(sec): 0.121807000
  • LastCPUTime(sec): 0.004337000
  • last_logical_reads: 218

03. 由 SSMS 的 GUI 介面，觀察 Execution Plan: ParameterList

  • Parameter Compiled Value: (9)
  • Parameter Runtime Value: (9)
  • Estimated Subtree Cost: 0.180413

-- figure 44_Actual_Estimate_row




04. 由 SSMS 的 GUI 介面，觀察 Execution Plan: Missing Index
  • Suggestion: Missing index

-- figure 45_Execution Plan_Missing_Index




05. 觀察 ParameterList element
  • ParameterCompiledValue="(9)" 
  • ParameterRuntimeValue="(9)"

-- figure 46_Show Execution Plan XML_9




06. 觀察 MissingIndexes element

-- figure 47_XML_Execution Plan_Missing_Index






Changing Parameter Values @StateProvinceID = 32


01. 執行  dbo.up_Parameter_Sniffing，使用 @StateProvinceID = 32

02. 觀察 Execution Plan
  • Parameterization 參數化: WHERE StateProvinceID= @StateProvinceID
  • @StateProvinceID = 9 與 32 ，都是使用 同一份 Execution Plan，減少 compile 所耗費的系統資源。
  • 但卻使用了 不適合 的 Execution Plan

-- figure 51_StateProvinceID_Bad Execution Plan



03. 觀察 ParameterList element
  • ParameterCompiledValue="(9)" 
  • ParameterRuntimeValue="(32)"

-- figure 52_Show Execution Plan XML_32




04. 由 SSMS 的 GUI 介面，觀察 Execution Plan:

  • Estimated Number of Rows: 19614
  • Estimated Number of Rows to Read: 1
  • Number of Rows Read: 4564
  • Actual Number of Rows: 19614

使用了 不適合 的 Execution Plan:

  • 實際上，因為 Index Scan，存取了 19614 rows
  • 實際上，僅需回傳 1 row
  • 原先預估，回傳的是 4564 rows
  • 原先預估，使用 Index Scan，存取了 19614 rows


-- figure 53_Compare_Actual_Estimate




05. 點選 Operator 之間的連接線:

  • Actual Number of Rows: 1
  • Number of Rows Read: 19614
  • Estimated Number of Rows: 4564

-- figure 54_Compare_Actual_Estimate




06. 由 SSMS 的 GUI 介面，觀察 Execution Plan: ParameterList
  • Parameter Compiled Value: (9)
  • Parameter Runtime Value: (3)
  • Estimated Subtree Cost: 0.180413

請注意
  • Estimated Subtree Cost 仍是 0.180413，這是 @StateProvinceID = 9 評估 的 Subtree Cost 。不是 @StateProvinceID = 32 的 Subtree Cost。 

-- figure 55_Actual_Estimate_row




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

-- figure 56_Look_At_last performance statistics



Summary

@StateProvinceID= 32

  • Bad Execution Plan
  • return: 1 rows
  • Index Scan
    • [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • Suggestion: Missing index
  • Table 'Address'. Scan count 1, logical reads 216, physical reads 0
  • LastElapsedTime(sec): 0.002388000
  • LastCPUTime(sec): 0.002387000
  • last_logical_reads: 218

Disadvantage of Parameter Sniffing

  • Maybe Reuse bad Execution Plan.
  • 因故 Reuse 到 不合適 的 Execution Plan，使得 效能 更糟，導致耗損 更多 的系統資源。
  • 因為 Side effect for Parameter Sniffing，也使得 Estimated Subtree Cost 不具備使用價值。


Sample Code

20171230_Parameter_Sniffing
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




參考資料

[SQL Server] Get SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile from Plan Cache - 子樹成本
http://sharedderrick.blogspot.tw/2017/12/sql-server-get-subtreecost-compiletime.html

[SQL Server] sys.dm_exec_query_stats: Get performance statistics from cached plan, 觀察 Plan Cache 中的效能統計資訊
http://sharedderrick.blogspot.tw/2017/12/sql-server-sysdmexecquerystats-get.html

[SQL Server] Look at parameter from execution plan, 查看 執行計畫 所使用的參數
http://sharedderrick.blogspot.tw/2017/12/sql-server-look-at-parameter-from.html

[SQL Server] Look at Execution Plan and index: use Index Hint, WITH(FORCESEEK), Missing Index
http://sharedderrick.blogspot.tw/2017/12/sql-server-look-at-execution-plan-and.html

Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues
https://blogs.msdn.microsoft.com/robinlester/2016/08/10/improving-query-performance-with-option-recompile-constant-folding-and-avoiding-parameter-sniffing-issues/

How OPTIMIZE FOR UNKNOWN Works
http://www.benjaminnevarez.com/2010/06/how-optimize-for-unknown-works/

Optimize for… Mediocre?
https://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/

Statistics on Computed Columns
Query Optimizer will use the 30% selectivity guess on inequality comparisons.
http://www.benjaminnevarez.com/2011/06/statistics-on-computed-columns/

Stored procedures, recompilation and .NetRocks
https://www.sqlskills.com/blogs/kimberly/stored-procedures-recompilation-and-netrocks/

Query Processing Architecture Guide
https://docs.microsoft.com/EN-US/sql/relational-databases/query-processing-architecture-guide#ParamSniffing

Cardinality Estimation (SQL Server)
https://docs.microsoft.com/EN-US/sql/relational-databases/performance/cardinality-estimation-sql-server

ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
https://docs.microsoft.com/EN-US/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql
於

沒有留言:

張貼留言