SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL

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)

, , ,
Previous Post
SQLAuthority News – Happy New Year – 5 SQL New Year Resolutions
Next Post
SQL SERVER – Time Delay While Running T-SQL Query – WAITFOR Introduction

Related Posts

71 Comments. Leave new

  • i tried with this query it is working great

    Reply
  • very helpful, thanks a lot for sharing it

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

    Reply
  • use glan berry diagnostic query

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

    Reply
  • i want to get the list of all queries which had executed before sql sever 2008 R2 restart can u please help me?

    Reply
  • Thanks Pinal..

    It was so helpful.

    Reply
  • 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,

    Reply
    • You need to capture “Attention” event via extended event.

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

  • Reply
  • pooja shinde
    May 30, 2017 9:00 pm

    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 ?

    Reply

Leave a Reply

Menu