Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to find high impact SQL Server queries with sys.dm_exec_query_stats

07 November,2012 by Tom Collins

There are different SQL Server  tuning approaches. A common approach is to track down long running transactions and optimise. The limitation  with that approach is it excludes short – running but high frequency queries .   Likewise, focusing only on short high frequency transactions excludes long  running transactions.

 The DMV sys.dm_exec_query_stats   returns  informations about cached query plans. When the cache is cleared , such as a restart , the information is cleared.

 A colleague of mine uses this query . This query  is ordered by sys.dm_exec_query_stats.total_elapsed_time. The total_elapsed_time column represents the Total elapsed time , in microseconds, for  successfully completed executions . Regardless of whether it has a high execution count or is a low execution count but long running transaction

 When users are complaining about severe problems , this query gives you a quick insight into the major query activity. It may not give you the root cause of an issue – therefore you many need to spend time on a more methodical approach such Waits and Queues analysis .

The troubleshooting process is improved if users report performance problems with greater detail. Use this guide :How to report a SQL Server performance problem

12th May 2022 - Added an extra column to display the database name 


	t.text as 'SQL Text'
	,db_name(t.dbid) as 'Database_name'
   ,ISNULL( st.total_elapsed_time / st.execution_count, 0 ) as 'AVG Excecution Time'
   ,st.total_worker_time / st.execution_count as 'AVG Worker Time'
   ,ISNULL( st.execution_count / DATEDIFF( second, st.creation_time, getdate()), 0 ) as 'Calls Per Second'

FROM sys.dm_exec_query_stats st
   CROSS APPLY sys.dm_exec_sql_text( st.sql_handle )  t
   st.total_elapsed_time DESC

 Read More on sql server performance 

Top 5 SQL Server DMV for Index Analysis - SQL Server DBA

SQL Server Performance Killers

SQL Server Performance Checklist

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to find high impact SQL Server queries with sys.dm_exec_query_stats | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer