How to find Longest running queries in SQL Server

29 October,2016 by Jack Vamvas

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

Read More

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server – Performance Top Queries by Total CPU Time 

Find Table Scans in Query Plan Cache

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

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

Working...

Post a comment on How to find Longest running queries in SQL Server


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer