UPDATE : Updated this query with bug fixed with one more enhancement SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2.
Recently my company owner asked me to find which query is running longest. It was very interesting that I was not able to find any T-SQL script online which can give me this data directly. Finally, I wrote down very quick script which gives me T-SQL which has ran on server along with average time and maximum time of that T-SQL execution. As I keep on writing I needed to know when exactly logging was started for the same T-SQL so I had added Logging start time in the query as well.
The reason I started to use this script to find out longest running query as if query is changed a bit it will display it as new row and new log start time. This way when I am improving T-SQL query or Stored Procedure I can check their progress in the query and does not have to get bothered with previous data.
I always run following DBCC command before I started to use my query. Following DBCC commands clears the cache of the server and starts fresh logging of the query running time.
DBCC FREEPROCCACHE
Run following query to find longest running query using T-SQL.
SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC GO
You can also add WHERE clause to above T-SQL query and filter additionally.
If you have not ran query like this previously on your server I strongly recommend to run this. I bet you will find surprising results.
Reference : Pinal Dave (https://blog.sqlauthority.com)
72 Comments. Leave new
Hi,
I’ve run that query on my server and i get intersting results.
By the way, MaxElapsedTime display the time in miliseconds?
Maximum elapsed time, in microseconds, for any completed execution of this plan
What’s the best equivalent way to perform this query in a SQL Server 2000 environment? (can’t use CROSS APPLY, sys.dm_exec_query_stats doesn’t exist)
I would be vary of running DBCC FREEPROCCACHE on production server cause it will cause recompilation of all the stored procs and on a OLTP kind of production server can be quite lethal.
Agree on the DBCC caution above
I modified it a bit as I got division by 0 errors, weird
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / NULLIF(s.execution_count,0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()),0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC –Maximum elapsed time, in microseconds
Perfect! I did too, great change and thanks, you saved me the time to fix this.
Try this instead.
select top 10 db_name(dbid),
total_worker_time/execution_count as avg_cpu_cost, plan_handle,
execution_count,
(select substring(text, statement_start_offset/2 + 1,
(case when statement_end_offset = -1
then len(convert(varchar(max), text))* 2
else statement_end_offset
end – statement_start_offset)/2)
from sys.dm_exec_sql_text(sys.dm_exec_query_stats.sql_handle)) as query_text
from sys.dm_exec_query_stats join sys.sysprocesses
on sys.dm_exec_query_stats.sql_handle = sys.sysprocesses.sql_handle
order by total_worker_time desc
I get syntax errors in SQL Server 2005
None of above instructions works in sql server 2000. it’s exclusively form sql 2k5 and 2k8 ??
1st by pinaldave:
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ‘APPLY’.
2nd by Jerry:
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ‘APPLY’.
3rd by jamie
Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near ‘max’.
I have run the above query but i get the following error.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘.’.
You need to run this against the “master” database. This will prevent the error from happening.
For SQL 2K .. I found from one of the website.. try it..
SELECT * FROM master..sysprocesses WHERE status = ‘runnable’ ORDER BY cpu desc
DBCC INPUTBUFFER (53)
DECLARE @handle binary(20)
SELECT @handle = sql_handle FROM master..sysprocesses WHERE spid = 53
SELECT [text] FROM ::fn_get_sql(@handle)
Is it possible to display the userid/username with the hostid ?
I found another query but the result from yours and this differ what is the different?
Can anyone explain please…
SELECT top 10
substring(text,qs.statement_start_offset/2
,(CASE
WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)
,qs.plan_generation_num as recompiles
,qs.execution_count as execution_count
,qs.total_elapsed_time – qs.total_worker_time as total_wait_time
,qs.total_worker_time as cpu_time
,qs.total_logical_reads as reads
,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
left JOIN sys.dm_exec_requests r
ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC
I want to become a master in sql please suggest me query which will show me a different from others.
Hi Pinal, really awesome stuff, as usual. But I agree with the comments above about using the freeproccache. Can you explain why it is that this is necessary? At least then people can assess the cost against the benefit. You say that you always run it, but do you know what happens if you do not? (Sorry, am reading this while not at a computer on which I can test his for myself.)
Pinal,
is it possible to list which user is running this long running query?
thanks
Hi
it’s simply awesome.
Thanks
Hi,
Its really great query.
Thanks Pinal
I have run the above query but i get the following error.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘.’.
Can any one pls suggest
@khan
Take the comma out of the last line of code, there is an extra comma after the word ‘DESC’
Hi I took out last comma also but its still giving me error
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘.’.
Pls any one help
For those with the:
Incorrect syntax near ‘.’.
problem… I think this is due to database compatabiltiy mode < 90. Still need to test to confirm, but I think that's my problem.
Pinal,
This helped identifying the query which takes time.
Is there a way to kill the long running procedure? In case SSRS report has executed that procedures?
Thanks,
Sid