德瑞克：SQL Server 學習筆記: [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)。

面對問題

已經上線的系統，如何觀察 SQL 陳述式的效能統計(例如：CPU 使用時間, Disk IO) 資料呢？
如何知道正在運行 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