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
very helpful, thanks a lot for sharing it
1. Open management studio
2. Start the profiler
3. right click on the server node, under reports, choose the report you want (performance i/o, writes etc)
4. the way to get the data for the reports can be seen in the profiler
use glan berry diagnostic query
i dont have permission to run the below query in QA. is there any other options to finding locked tables.
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
i want to get the list of all queries which had executed before sql sever 2008 R2 restart can u please help me?
Thanks Pinal..
It was so helpful.
Dave, I know this is an old post, but it appears that only queries that complete are stored in sys.dm_exec_query_stats. I want to report on all queries, even if they are cancelled. A query could be executed and run for 12 hours, then the user cancels it and it doesn’t appear in sys.dm_exec_query_stats. How can I find the execution times of queries that were cancelled – is there a sql server table that stores this info? Thanks,
You need to capture “Attention” event via extended event.
An example would be helpful. I have loved and used your scripts for over 15 years! Thank you!!!
I also would like clarification on the freecache. I would run the query, analyze the output, then run DBCC FREEPROCCACHE in order to see what else comes up after changes I would make using the stats that were first found. If I didn’t trust the stats then I would run DBCC FREEPROCCACHE first and wait to check. Thanks!
Thanks!
i have identified a slow running sp in my server.I want fix it can u help me in that.
I already tried with recompile of sp and rebuiting the execution plans and index rebuit and reorg.But nothing happen the result is same
hello, had one query ? In a table w have 5 columns named id,id2, address1,address2,address3..this table has many wrong addresses but some addresses are correct one.. I want to remove those incorrect addresses how ?
Thank you for the detail