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
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 you can join these with the help of ‘sql_handle’
‘sql_handle’ column.
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.
i tried with this query it is working great