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 (http://blog.SQLAuthority.com)
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
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
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
Pingback: SQL SERVER - 2008 - 2005 - Find Longest Running Query - TSQL - Part 2 Journey to SQL Authority with Pinal Dave
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
Awesome stuff as usual, Pinal. But the URL offered at the top of the entry above is is wrong. Where it has 1/22 in it:
http://blog.sqlauthority.com/2009/01/22/sql-server-2008-2005-find-longest-running-query-tsql-part-2/
it should instead be 1/23:
http://blog.sqlauthority.com/2009/01/23/sql-server-2008-2005-find-longest-running-query-tsql-part-2/
Feel free to delete this comment once you’ve updated the entry here. Keep up the great work, and thanks for all you share.
Thanks Charlie,
I respect your comment correcting me.
Kind Regards,
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
still got the
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ‘–’.
on sql server 2005
can anyone help pls
Post the code you used
Sounds like the comment at the end is missing a – to make it :
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
What seems to have happened is some autoformatting in windows, replacing — with –
Thank you. That query saved our production server !
How long is the data retained in sys.dm_exec_query_stats and sys.dm_exec_sql_text?
The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
@khan
“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”
You are running an MS SQL database on a 2005 or 2008 server but the DB in 2000 compatibility mode. Up the compatibility to server 2005 and you’ll fix it.
Ian
Ian Brown
Hi Ian,
Just for information, I’m running the DB on our server in 2000 compatability mode also, and found that the way to resolve the issue mentioned, was to run the query against the “master” database, rather than our databases.
Regards
Andy Bassitt
Hi Pinal,
I wann learn Microsoft sql server…..
would u sugesst me and give me tips regarding this…
How to learn the data base would u assist me..
hi guys ,
i want to find the queries ran and their time. , who ran it from which program.
the first part is solved by
————-sql 1———-
use master
SELECT
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
s.creation_time AS LogCreatedOn
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, ExecutionCount DESC
second part gives database name hostname , program name , nt username and loginname
———–sql 2———-
SELECT DATEDIFF(MINUTE, a.last_batch, GETDATE()) RunningTime,
a.spid, a.blocked, a.waittime, db_name(a.dbid) As dbname,
a.last_batch, a.hostname, a.program_name,
a.nt_username, a.loginame, b.text as sqlstatement
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) B
ORDER BY a.last_batch DESC
I need your help to combine query 1 and 2 as i am not familiar with cross apply . please help
Awesome I got it what iw as looking for
Thank you so much for posting this query..
Thanks a lot sreeKumar. If you find any script which combines both plz post it, would be much helpful.
SELECT t.TEXT AS sqlstatement,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
s.creation_time AS LogCreatedOn,
Datediff(MINUTE, a.last_batch, Getdate()) RunningTime,
a.spid,
a.blocked,
a.waittime,
Db_name(a.dbid) AS dbname,
a.last_batch,
a.hostname,
a.program_name,
a.nt_username,
a.loginame
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.sysprocesses A
CROSS APPLY sys.Dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_elapsed_time DESC,
ExecutionCount DESC
HI Someone,
Thanks for posting the query.I tried your query, it looks fine but takes a bit more time to execute the statement. Am not good into developing, is there any way to may this statement run quickly? Thanks for your post. it indeed helpful.
This query is not right … Cannot do a cross apply of sys.sysprocesses to sys.dm_exec_query_stats . so you need to take out the first cross apply and the corresponding entries for sys.sysprocesses in the select stmt ( i.e a.spid, a.blocked …. a.loginname)
Hi,
I want to do SQL server 2005 certification.
Can anyone please provide the DUMPS for the same.
Thanks in advance!
Gunjan Kalra
You could just try studying using the Microsoft guides… thats what I did and it worked just fine.
thanks sir ,
please tell me abt function,procedure,trigger and joining
sumit dave, thanks again.
HI all,
My requirements are:-
1) How many queries are hitting database
2) What are the queries
3) Which queries is taking long time to execute.
I m using database oracle. Please help.
Thanks in advance.
I tried all queries mentioned above but none of them is working :(
HI all,
My requirements are:-
1) How many queries are hitting database
2) What are the queries
3) Which queries is taking long time to execute.
I m using database oracle. Please help.
I tried all queries mentioned above but none of them is working :(
Thanks in advance.
Hi, I think that your problem is that these queries are written for Microsoft T-SQL Server, so they will be different syntax to PL/SQL.
Oracle doesnt have the same system DMV’s that SQL has, which is probably what your problem is.
Correction, they are written in T-SQL for Microsoft SQL Server. (Not, as stated “Written for MIcrosoft T-SQL Server”).
However the fact remains that the sys DMV’s are not present in Oracle.
I suggest starting with the v$sqlstats view (in Oracle) and trying to figure out what you need from there.
Is there any query where we can see longest running queries since last restart server….. Thanks in advance
SQLDBA
Hi team can anyone confirm if we can get database name as well along with top 10 long running quries.
It seems it doesn’t work on 2005 :(
Hi Any one please help for top 10 long running Queries for 24 hrs in one Instance.
Pinal,
I have used begin transaction in stored procedure that sp used about 1500 To 2000 times in one day for insert the record in 4 tables and fired the some triggers on two tables then updates the 3 to 4 tables records.some time my transaction come in sleeping mode. then i kill the transaction then insert the record.Plz tell the sol.
Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority
Simply Great talaivaa
i tried with this query it is working great