Microsoft – SQL Server – System Review – Queries – sys.dm_exec_query_stats

Microsoft – SQL Server – System Review – Queries – sys.dm_exec_query_stats

One of things you want to take a look at when reviewing SQL Server Instances are the Dynamic Management Views.

A good DMV is sys.dm_exec_query_stats.  It contains the following columns:

  • execution_count
  • total_worker_time
  • total_physical_reads
  • total_logical_writes
  • total_logical_reads
  • last_logical_reads
  • total_elapsed_time
  • total_rows
  • last_execution_time

Query



/*

    exec sp_help 'sys.dm_exec_query_stats'

*/

/*

    exec sp_help 'sys.dm_exec_query_stats'

*/

/*

    exec sp_help 'sys.dm_exec_query_stats'

*/
select top 50

	  max(tblSQL.text) as query

	, sum(tblQueryStat.execution_count) as executionCount

	, SUM(tblQueryStat.total_physical_reads)
           as totalPhysicalReads
	, SUM(tblQueryStat.total_logical_reads)
            as totalLogicalReads	

	, SUM(tblQueryStat.total_logical_writes) 
             as totalLogicalWrites

	, sum(tblQueryStat.total_rows) as totalRows

	, SUM(tblQueryStat.total_worker_time) as totalWorkerTime	
	, SUM(tblQueryStat.total_elapsed_time) as totalElapsedTime
	, (SUM(tblQueryStat.total_elapsed_time)
              - SUM(tblQueryStat.total_worker_time))   
	          as diffElapsedTimeAndWorkerTime	

	, convert(varchar(30), max(last_execution_time), 100) 
	      as last_execution_time

from  sys.dm_exec_query_stats tblQueryStat

	cross apply sys.dm_exec_sql_text (tblQueryStat.sql_handle) tblSQL

group by tblQueryStat.plan_handle

order by       
              totalElapsedTime desc  
            , totalWorkerTime desc

There are a couple of areas you might be surprised:

Time:

  • Differences between Elapsed Time (Total time to run query) and Execution time (Time spent by CPU).  
  • The difference is caused by waits (IO, Parallelism)

Logical Reads and Physical Reads:

  • Logical Reads –> Reads request
  • Physical Reads –> Reads that were actually conducted against the Disks
  • Logical Reads – Physical Reads –> Reads satisfied through other means rather than I/O

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s