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
於

沒有留言:

張貼留言