SQL Server – Query Stats – What Queries are dragging your system down

Prerequisite

The sys.dm_exec_query_stats DMV only started exposing row-count information as on Denali and so the SQL pasted below is only applicable to SQL Server 2012 and later.

Actual Query

Reading through online performance monitoring tools brought me once again to MS SQL Server’s Dynamic Management Views; specifically sys.dm_exec_query_stats:


select 
	  tblQueryStat.total_worker_time
	, tblQueryStat.total_elapsed_time				  
	, tblQueryStat.total_rows  
	, tblQueryStat.total_physical_reads
	, tblQueryStat.total_logical_reads		
	, tblQueryStat.total_logical_writes
	, tblSQLText.text
	, [numberofRowsEstimated]
              = tblQueryPlan.[query_plan].value
             (
                    '(//@EstimateRows)[1]'
                  , 'varchar(128)'
             ) 
	, tblQueryStat.last_rows as nunberofRowsActual
	, tblQueryStat.execution_count
        , tblQueryStat.plan_generation_num

from   sys.dm_exec_query_stats tblQueryStat

cross apply sys.[dm_exec_sql_text](tblQueryStat.sql_handle) 
      as tblSQLText

cross apply sys.[dm_exec_query_plan](tblQueryStat.plan_handle) 
      as tblQueryPlan

order by 
	 tblQueryStat.total_worker_time desc

Our results confirmed what we knew all along:

AggregatedQueryStats

By far our biggest culprit was this one query.

But, also was able to confirm the following:

  • That we have a lot of parallelism going on — this is confirmed due to the fact that the total_worker_time is a bit greater than total_elapsed_time
  • We were not terribly skewed per estimated number of rows and actual number of rows.  If actual number of rows were not aligned as to estimated number of rows, then it will be advisable to update the statistics
  • Execution Count is also note worthy
  • As is plan_generation_num — when high it means the query plan is constantly being re-generated. When so, see about using Stored Procedures.  And, if within SP’s see if there are statements within you SP that is causing the query plans to be re-generated

Confessionally, I wish life was  good.  And, I could have as much fun @ work as say “Joshua Radin”.

 

Listening in 

Joshua Radin – In her eyes
http://www.youtube.com/watch?feature=endscreen&v=0cXKIER2jVk&NR=1

References:

One thought on “SQL Server – Query Stats – What Queries are dragging your system down

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