Quite often a customer asks me how to find the longest running queries on a SQL Server . The goal is find queries which run for a long time and also to report on how frequently the queries execute.
This query is different from SQL Server – Find high impact queries with sys.dm_exec_query_stats – which focused on the highest average query.
This query will help you discover queries which you may not realise take such a long time. For example maintenance queries running more often than necessary.
I also like to look at the logical reads and assess whether there are opportunities for index tuning and sql query tuning
SELECT DISTINCT TOP 20 est.TEXT AS QUERY , Db_name(dbid), eqs.execution_count AS EXEC_CNT, eqs.max_elapsed_time AS MAX_ELAPSED_TIME, ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count,0), 0) AS AVG_ELAPSED_TIME, eqs.creation_time AS CREATION_TIME, ISNULL(eqs.execution_count / NULLIF(DATEDIFF(s, eqs.creation_time, GETDATE()),0), 0) AS EXEC_PER_SECOND, total_physical_reads AS AGG_PHYSICAL_READS FROM sys.dm_exec_query_stats eqs CROSS APPLY sys.dm_exec_sql_text( eqs.sql_handle ) est ORDER BY eqs.max_elapsed_time DESC