Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to find Longest running queries in SQL Server

29 October,2016 by Tom Collins

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.



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


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to find Longest running queries in SQL Server | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer