How to find Longest running queries in SQL Server

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   How to Find high impact SQL queries with sys.dm_exec_query_stats   – which focused on the highest average sql 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.

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

I also like to look at the logical reads and assess whether there are opportunities for index tuning and sql query tuning

Once you've identified a long running SQL Query  use this simple guide on  How to troubleshoot a slow running query in SQL Server

Read More on troubleshooting SQL Performance Tuning 

How to Find high impact SQL queries with sys.dm_exec_query_stats

SQL Server – Performance Top Queries by Total CPU Time 

Find Table Scans in Query Plan Cache