Discover Long-Running Queries

Let SQL Server tell you which queries take the longest to run. The following query returns the five longest-running queries. You can change the TOP 5 to whatever number best meets your needs.

SELECT TOP 5 t.TEXT query, 
   stats.max_elapsed_time 
AS MaxElapsedTime,
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC