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