[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

Adaptive Cursors and SQL Plan Management
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html

Improve SQL Query Performance by Using Bind Variables
https://blogs.oracle.com/sql/improve-sql-query-performance-by-using-bind-variables

[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

[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