搜尋本站文章

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

2017-12-23

[SQL Server] Get SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile from Plan Cache - 子樹成本


在執行 SQL 陳述式的 Performance Tuning 時,CPU 使用時間、Disk IO 使用量等 是 觀察 重點。
請參考這篇: [SQL Server] sys.dm_exec_query_stats: Get performance statistics from cached plan, 觀察 Plan Cache 中的效能統計資訊

還可以觀察由 Execution Plan 所提供的 SubTreeCost (子樹成本)。
但要提醒的是: SubTreeCost 有時會有不精確的問題,例如:Parameter Sniffing 等,我們將在後續主題討論。

本文利用 DMV 取得 Plan Cache 內的 SubTreeCost, CompileTime, CompileMemory, Execution Count, RetrievedFromCache 等資訊。





-- Get execution plan for cached query: Execution Count, Plan Recompile, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
TRY_CAST(qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') AS decimal(38,7))
 StatementSubTreeCost,
qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
qp.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@RetrievedFromCache)[1]', 'nvarchar(50)') [RetrievedFromCache],
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
qp.query_plan [QueryPlan], cp.plan_handle
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;



Get execution plan from DMV: SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile


01. 執行以下的 Adhoc Query:


-- figure 01_Adhoc_Query



02. 使用 DMV: sys.dm_exec_query_stats 觀察 Plan Cache
  •  Execution Plan, Execution Count, Plan Recompile, Query Plan, etc.
-- figure 02_Get execution plan for cached query_Execution Count, Plan Recompile, Query Plan, etc.




-- figure 03_Get execution plan for cached query_Execution Count, Plan Recompile, Query Plan, etc.




-- Get execution plan for cached query: Execution Count, Plan Recompile, Query Plan, etc.

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan [QueryPlan], cp.plan_handle
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;





使用 XQuery 由 query_plan 取得 SubTreeCost


01. 使用 DMV: sys.dm_exec_query_stats 觀察 Plan Cache

  • SubTreeCost, CompileTime(ms), CompileCPU(ms), 
  • CompileMemory(KB), RetrievedFromCache, ExecutionCount, PlanRecompile, etc.

-- figure 04_Get execution plan for cached query_Execution Count, Plan Recompile, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.



  • 當 StateProvinceID = 32,SubTreeCost 是 0.0065704

-- figure 05_Get execution plan for cached query_Execution Count, Plan Recompile, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.




02. 觀察 SSMS GUI 的 Execution Plan 之 Properties 視窗。
  • 當 StateProvinceID = 32,SubTreeCost 是 0.0065704
-- figure 33_GUI_Execution_Plan






在撰寫 XQuery 時,要留意資料類型

01. 遇到一段特別 SubTreeCost 的 SQL 陳述式

-- figure 61_Special_SubTreeCost



  • SubTreeCost 是 0.0000012
-- figure 62_Special_SubTreeCost_Execution_Plan




02. 觀察 XML 格式的 Execution Plan
  • StatementSubTreeCost="1.157E-06"
-- figure 63_XML_StatementSubTreeCost



03. 正確顯示,與 GUI 介面的 Execution Plan,能顯示出一致的數值:0.0000012。
  • XQuery 使用資料類型 float ,再使用 TRY_CAST 轉型為 decimal(38,7)
-- figure 64_XML_StatementSubTreeCost





清空 Cache

請勿於 Production 環境執行

-- figure 301_Remove_Cache






範例程式碼

20171223_Get_execution_plan_from_DMV
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





[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

Display and Save Execution Plans
https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-and-save-execution-plans

2017-12-21

[SQL Server] sys.dm_exec_query_stats: Get performance statistics from cached plan, 觀察 Plan Cache 中的效能統計資訊


在 開發環境(Development Environment)上,若要觀察 SQL 陳述式執行的效能統計資料,可以使用 SET STATISTICS TIME, SET STATISTICS IO:

  • SET STATISTICS IO: 顯示 SQL 陳述式 所產生之磁碟活動量的相關資訊。
  • SET STATISTICS TIME: 顯示剖析、編譯和執行每個 SQL 陳述式所需要的毫秒數(milliseconds)。

面對問題

  1. 已經上線的系統,如何觀察 SQL 陳述式 的 效能統計(例如:CPU 使用時間, Disk IO) 資料 呢?
  2. 如何知道正在運行 SQL 陳述式 的 效能統計 資料 呢?




可以使用 sys.dm_exec_query_stats,觀察 Plan Cache 中的效能統計資訊。

若從 Plan Cache 移除 Execution Plan 時,對應的資料列也會從這個 DMV 中刪除。

01. 使用 sys.dm_exec_query_stats,觀察 Plan Cache 中的效能統計資訊

-- figure 01_Get performance statistics for cached query




-- Get performance statistics for cached query
/*
sys.dm_exec_query_stats - Returns aggregate performance statistics
An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. 
More accurate results may be determined by rerunning the query.

total_elapsed_time: Total elapsed time, reported in microseconds (but only accurate to milliseconds), 
for completed executions of this plan.
*/
USE master

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], cp.usecounts [ExecutionCount],
 qs.total_elapsed_time/cp.usecounts/1000000.0 [AvgElapsedTime(sec)], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)], qs.max_elapsed_time/1000000.0 [MaxElapsedTime(sec)], 
 qs.min_elapsed_time/1000000.0 [MinElapsedTime(sec)], qs.total_elapsed_time/1000000.0 [TotalElapsedTime(sec)],
 qs.total_worker_time/cp.usecounts/1000000.0 [AvgCPUTime(sec)], 
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)], qs.max_worker_time/1000000.0 [MaxCPUTime(sec)], 
 qs.min_worker_time/1000000.0 [MinCPUTime(sec)], qs.total_worker_time/1000000.0 [TotalCPUTime(sec)], 
 qs.total_logical_reads/cp.usecounts [AvgLogicalRead], 
 qs.last_logical_reads, qs.max_logical_reads, qs.min_logical_reads, qs.total_logical_reads, 
 qs.last_physical_reads [LastPhysicalRead], qs.max_physical_reads, qs.min_physical_reads, qs.total_physical_reads,  
 qs.total_logical_writes/cp.usecounts [AvgLogicalWrite], qs.last_logical_writes,
 qs.max_logical_writes, qs.min_logical_writes, qs.total_logical_writes,
 qs.last_rows [LastRows], qs.max_rows, qs.min_rows, qs.total_rows, 
 qp.query_plan [QueryPlan], cp.plan_handle, cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 st.objectid, qs.last_execution_time, qs.creation_time --qs.*
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;





sys.dm_exec_query_stats: Get performance statistics from cached plan

Enable both of the options: Disk IO, CPU

01. 啟用 STATISTICS IO  STATISTICS TIME

  • 若要一併啟用,可以寫成:

-- Enable both of the options: Disk IO, CPU
SET STATISTICS IO,TIME ON;


-- figure 11_Enable both of the options_Disk IO_CPU




02. 建立 Stored Procedure: up_StateProvinceID_MissingIndex

  • Table: Person.Address

-- figure 12_Create_Proc_up_StateProvinceID_MissingIndex





Look at performance statistics 


01. 執行此 Stored Procedure: up_StateProvinceID_MissingIndex,觀察 STATISTICS IO,TIME:

  • logical reads: 216

-- figure 13_Look_at_STATISTICS_IO_TIME




02. 觀察 Execution Plan:

  • Return: 4564 rows
  • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • Suggestion: Missing Index

-- figure 14_Execution_Plan_Missing_Index






Look at sys.dm_exec_query_stats

01. 觀察 Logical Read

  • Logical Read: 218

-- figure 21_Logical_Read



02. 觀察 Physical Read

  • Physical Read: 219

-- figure 22_Physical_Read



03. 觀察 回傳資料列的筆數

  • Row count: 4564
  • Total rows: 9128, 因為執行 2 次

-- figure 23_return_row



04. 觀察 該 SQL 陳述式的 Plan Cache

  • plan_generation_num: 1,該 SQL 陳述式的 Recompile 次數。
  • last_execution_time: 最近一次的執行日期時間。
  • creation_time: 建立 Plan 的日期時間。

-- figure 24_Cache_Info





Using Missing Index to CREATE INDEX 


01. 使用 Missing Index 資訊 建立 INDEX

-- figure 31_Create_Index_StateProvinceID_City







Look at performance statistics 

01. 再度執行此 up_StateProvinceID_MissingIndex,觀察 STATISTICS IO,TIME:

  • logical reads: 24,先前是  216

-- figure 32_Look_At_STATISTICS_IO_TIME





02. 觀察 Logical Read

  • Logical Read: 24

-- figure 33_Logical_Read




03. 觀察 該 SQL 陳述式的 Plan Cache
  • plan_generation_num: 2,原先是 1,因為有效能更好的 Index 可使用,系統自動 recompile 該 SQL 陳述式的 Execution Plan。

-- figure 34_Cache_Info





Look at sys.dm_exec_query_stats


01. 觀察 sys.dm_exec_query_stats,可以看到:

  • TSQL
  • DB Name
  • Execution Count

-- figure 41_Cache_Info




02. 觀察 sys.dm_exec_query_stats,可以看到:

  • Elapsed Time: 此 Execution Plan 的執行時間。(以毫秒 microseconds 為單位來報告,但是精確度只到毫秒 milliseconds)。
  • AvgElapsedTime(sec)
  • LastElapsedTime(sec)
  • MaxElapsedTime(sec)
  • MinElapsedTime(sec)
  • TotalElapsedTime(sec)

-- figure 42_ElapsedTime




03. 觀察 sys.dm_exec_query_stats,可以看到:

  • Worker Time (CPU Time):  此 Execution Plan 所耗用 CPU 時間。(以毫秒 microseconds 為單位來報告,但是精確度只到毫秒 milliseconds)。
  • AvgCPUTime(sec)
  • LastCPUTime(sec)
  • MaxCPUTime(sec)
  • MinCPUTime(sec)
  • TotalCPUTime(sec)

-- figure 43_CPU_Time




04. 觀察 sys.dm_exec_query_stats,可以看到:
  • 回傳資料列的筆數
  • QueryPlan: XML 格式的 Execution Plan.
  • plan_handle

-- figure 44_Row_Count






注意

初始查詢sys.dm_exec_query_stats工作負載正在執行伺服器上是否可能會產生不正確的結果。
您可以重複執行查詢,以找出較精確的結果。

-- 45_might produce inaccurate results







範例程式碼

20171221_Get_performance_statistics
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




參考資料

[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

SET STATISTICS TIME (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql

SET STATISTICS IO (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql

sys.dm_exec_query_stats (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql