SQL Server – Find high impact queries with sys.dm_exec_query_stats

07 November,2012 by Jack Vamvas

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

 

. SELECT TOP 5
   t.text as 'SQL Text', 
   st.execution_count , 
   ISNULL( st.total_elapsed_time / st.execution_count, 0 ) as 'AVG Excecution Time',
   st.total_worker_time / st.execution_count as 'AVG Worker Time',
   st.total_worker_time,
   st.max_logical_reads, 
   st.max_logical_writes, 
   st.creation_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
ORDER BY 
   st.total_elapsed_time DESC

 Read More

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

SQL Server Performance Killers

SQL Server Performance Checklist


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on SQL Server – Find high impact queries with sys.dm_exec_query_stats


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer