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