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

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 

SELECT TOP 5
    
	t.text as 'SQL Text'
	,db_name(t.dbid) as 'Database_name'
   ,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 on sql server performance 

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

SQL Server Performance Killers

SQL Server Performance Checklist