SQL server- Find long running database queries

SQL server- Find long running database queries

August 6th, 2010|Technical|

On several occasions,  we run into daunting situations  of finding long running and inefficient queries, plaguing our database . Task becomes even more challenging when you are not familiar with the application.

We found following queries to be useful in detecting problematic queries.

1. Query will list queries (T SQL, Stored procs, Functions) etc, which are taking most of  SQL server time
SELECT   dest.TEXT AS
[Query], SUM(execution_Count) as execution_Count,SUM(total_elapsed_time) as total_elapsed_time,
SUM(total_worker_time)as total_worker_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where deqs.last_execution_time between GETDATE()-1 and GETDATE()
group by dest.TEXT
ORDER BY  SUM(total_worker_time) desc , sum(total_elapsed_time) DESC
2. Same as above query with slight variation
SELECT  deqs.last_execution_time AS [Time], dest.TEXT AS [Query],creation_time,execution_Count,total_elapsed_time,
last_elapsed_time,
total_worker_time, total_logical_reads
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where deqs.last_execution_time between GETDATE()-1 and GETDATE()
ORDER BY  total_elapsed_time DESC