How to Find Long Running Queries in SQL Server

Long Running Queries is one of the most common issue in every clients.To troubleshoot Long running Queries we have to identify first.Run below query to find the Long Running Queries in SQL Server.

SELECT DISTINCT TOP 10
tq.TEXT AS QUERY ,
Db_name(dbid),
lq.execution_count AS EXEC_CNT,
lq.max_elapsed_time AS MAX_ELAPSED_TIME,
ISNULL(lq.total_elapsed_time / NULLIF(lq.execution_count,0), 0) AS AVG_ELAPSED_TIME,
lq.creation_time AS CREATION_TIME,
ISNULL(lq.execution_count / NULLIF(DATEDIFF(s, lq.creation_time, GETDATE()),0), 0) AS EXEC_PER_SECOND,
total_physical_reads AS AGG_PHYSICAL_READS
FROM sys.dm_exec_query_stats lq
CROSS APPLY sys.dm_exec_sql_text( lq.sql_handle ) tq
ORDER BY
lq.max_elapsed_time DESC

Backup and Restore Interview Question and Answers

SQL Server installation interview Questions and Answers

 

Get SQL Dba Tutorial Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Add Comment