Parent, SQL Exec Stats, Copyright © 2006-2018 Elemental Inc. All rights reserved.
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, ExecStats, StoredProcs, DistStats, IxOpStats, Appendix

update in progress 2015-Feb

Execution Stats

As mentioned earlier, Exec Stats has several modes. The default is statement. The details of each mode are describe below along with SQL. The details and options will change as Exec Stats evolves.

Mode

The first set columns are aggregations from dm_exec_query_stats. After Plan Generation (PGenN) are information extracted from the XML plan, including: compile cost, plan cost, estimated number of rows, the number of steps in the plan, number of parallel execution steps, the number of scans, inserts, updates and delete, and finally the compile parameters.

Stmt

The second set of columns.

Stmt

The third set of columns.

Stmt

The fourth set of columns.

Stmt

The final set of columns.

Stmt

Statement Mode

Statement mode was originally every single row in dm_exec_query_stats. Any given SQL statement will have a different entry in dm_exec_query_stats if it has a different execution plan, or if it is executed in a different database even if the plan is the same, (or if there are other conditions that are different), as represented by the plan_handle and statement start and end offsets.
Statement mode is currently implemented as shown below. The grouping is by sql_handle, which represents an entire batch, and the statement offsets, which represents the individual statements within a batch. So all different plan handles for a given SQL are aggregated together.
I have noticed that Activity Monitor groups by query hash and query plan hash, (which only works for SQL Server 2008)

 

Exec Stats SQL Statement

-- Exec Stats Sql Handle Cnt:

WITH SqlH AS (
 SELECT sql_handle, statement_start_offset, statement_end_offset,
  MAX(plan_generation_num)  plan_generation_num,
  SUM(execution_count)    execution_count,
  SUM(total_worker_time)    total_worker_time,
  MAX(max_worker_time)     max_worker_time,
  SUM(total_physical_reads) total_physical_reads,
  SUM(total_logical_writes) total_logical_writes,
  SUM(total_logical_reads)  total_logical_reads,
  SUM(total_clr_time)       total_clr_times,
  SUM(total_elapsed_time)   total_elapsed_time,
  MAX(max_elapsed_time)     max_elapsed_time,
  COUNT(*) AS StmtCnt 
 FROM sys.dm_exec_query_stats
 GROUP BY sql_handle, statement_start_offset, statement_end_offset
)
SELECT COUNT(*) AS Cnt ,
 MAX(plan_generation_num)  plan_generation_num,
 SUM(execution_count)      execution_count,
 SUM(total_worker_time)    total_worker_time,
 SUM(total_physical_reads) total_physical_reads,
 SUM(total_logical_writes) total_logical_writes,
 SUM(total_logical_reads)  total_logical_reads,
 SUM(total_clr_time)    total_clr_time,
 SUM(total_elapsed_time)   total_elapsed_time,
 MAX(max_worker_time)      max_worker_time,
 MAX(max_elapsed_time)     max_elapsed_time,
 SUM(StmtCnt) AS StmtCnt
FROM SqlH

GO

 

-- Exec Stats Sql Handle:

WITH SqlH AS (
 SELECT TOP 1000 sql_handle,
  statement_start_offset,   statement_end_offset,
  MAX(plan_generation_num)  plan_generation_num,
  MAX(plan_handle)          plan_handle,
  MIN(creation_time)        creation_time,
  MAX(last_execution_time)  last_execution_time,
  SUM(execution_count)      execution_count,
  SUM(total_worker_time)    total_worker_time,
  MAX(max_worker_time)      max_worker_time,
  SUM(total_physical_reads) total_physical_reads,
  SUM(total_logical_writes) total_logical_writes,
  SUM(total_logical_reads)  total_logical_reads,
  SUM(total_clr_time)       total_clr_time,
  SUM(total_elapsed_time)   total_elapsed_time,
  MAX(max_elapsed_time)     max_elapsed_time,
  MAX(query_hash)     query_hash,
  MAX(query_plan_hash)     query_plan_hash,
  COUNT(*) AS StmtCnt  ,
  ROW_NUMBER() OVER (ORDER BY SUM( total_worker_time) DESC) AS RowNum
 FROM sys.dm_exec_query_stats
 GROUP BY sql_handle, statement_start_offset, statement_end_offset
)
SELECT sql_handle, statement_start_offset, statement_end_offset,
 plan_generation_num,  plan_handle,
 creation_time,    last_execution_time,
 execution_count,
 total_worker_time,   max_worker_time,
 total_physical_reads,
 total_logical_writes, total_logical_reads,
 total_clr_time,
 total_elapsed_time,  max_elapsed_time,
 query_hash,        query_plan_hash,
 StmtCnt, RowNum
FROM SqlH

GO

Exec Stats Batch

Batch was originally the aggregation of a given batch, or sql plan handle in dm_exec_query_stats. Currently is the aggregation of the entire batch, plus the individual statements. Below is the output in batch mode.

 

-- Exec Stats Batch:

WITH Batch AS (
 SELECT TOP 1000   sql_handle,
  MIN(statement_start_offset) statement_start_offset,
  MAX(plan_generation_num)  plan_generation_num,
  MAX(plan_handle)          plan_handle,
  MIN(creation_time)        creation_time,
  MAX(last_execution_time)  last_execution_time,
  AVG(execution_count)      execution_count,
  SUM(total_worker_time)    total_worker_time,
  MAX(max_worker_time)      max_worker_time,
  SUM(total_physical_reads) total_physical_reads,
  SUM(total_logical_writes) total_logical_writes,
  SUM(total_logical_reads)  total_logical_reads,
  SUM(total_clr_time)       total_clr_time,
  SUM(total_elapsed_time)   total_elapsed_time,
  MAX(max_elapsed_time)     max_elapsed_time,
  MAX(query_hash)     query_hash,
  MAX(query_plan_hash)      query_plan_hash,
  COUNT(*) AS StmtCnt  ,
  ROW_NUMBER() OVER (ORDER BY SUM(total_worker_time) DESC) AS RowNum
 FROM sys.dm_exec_query_stats
 GROUP BY sql_handle --, plan_handle
)
SELECT sql_handle,
 statement_start_offset,-1 statement_end_offset,
 plan_generation_num, plan_handle,
 creation_time, last_execution_time,
 execution_count,
 total_worker_time, max_worker_time,
 total_physical_reads,
 total_logical_writes, total_logical_reads,
 total_clr_time,
 total_elapsed_time, max_elapsed_time,
 query_hash, query_plan_hash,
 StmtCnt, RowNum
FROM Batch
UNION ALL
SELECT  s.sql_handle,
 s.statement_start_offset,  s.statement_end_offset,
 s.plan_generation_num, s.plan_handle,
 s.creation_time, s.last_execution_time,
 s.execution_count,
 s.total_worker_time, s.max_worker_time,
 s.total_physical_reads,
 s.total_logical_writes, s.total_logical_reads,
 s.total_clr_time,
 s.total_elapsed_time, s.max_elapsed_time,
 s.query_hash, s.query_plan_hash,
 0 StmtCnt, RowNum
FROM Batch b
INNER JOIN sys.dm_exec_query_stats s ON s.plan_handle = b.plan_handle
WHERE StmtCnt > 1 AND s.statement_start_offset > b.statement_start_offset
ORDER BY RowNum, statement_start_offset

Exec Stats Plan Cache

Below is Plan Cache. Notice execution statistics is not available.


Plan Cache c
Plan Cache

-- Plan Cache:

SELECT  bucketid, refcounts, usecounts, size_in_bytes, objtype, plan_handle
FROM sys.dm_exec_cached_plans WHERE cacheobjtype = 'Compiled Plan'

The XML plan (obsolete)

This tab was in earlier versions, and has since been disabled.

Clicking on the SQL column in Exec Stats will shift to the tab page XML Plan. Also use the < and > buttons to navigate XML plans. Click the button “Save XML Plans” to save each execution plan as a separate sqlplan file.


Plan TreeNode

Plan TreeNode

Plan TreeNode

Show Plan

The Show Plan tab is similar to the plan in the old SQL Server 2000 tabular format.


ShowPlan

ShowPlan

ShowPlan

ShowPlan

ShowPlan

ShowPlan

 

Prev        Next