Home
QUERIES
How to Find Long Running Queries in SQL Server

How to Find Long Running Queries in SQL Server

No Comments

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.

Related Posts

About The Author

Sasidhar
More from this Author

This is Konduru Sasidhar and most of the friends call me SASI for short. I have 8 years of experience in SQL database administration and Business Intelligence. I’ve started developing this blog to share right information to the people who are interested in SQL DBA.In my experience, I faced many issues affiliates of SQL DBA Administration and Business Intelligence job. I’m collecting the major scenarios and publishing the same on this blog.

Add Comment