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 (http://blog.SQLAuthority.com)

About these ads

65 thoughts on “SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL

  1. Hi,
    I’ve run that query on my server and i get intersting results.

    By the way, MaxElapsedTime display the time in miliseconds?

  2. 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)

  3. 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.

  4. 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

  5. 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

  6. 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’.

  7. 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)

  8. 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

  9. Pingback: SQL SERVER - 2008 - 2005 - Find Longest Running Query - TSQL - Part 2 Journey to SQL Authority with Pinal Dave

  10. 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.)

  11. 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.

  12. 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

  13. 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

  14. 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.

  15. 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

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

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

  16. @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

  17. 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..

  18. 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

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

  19. Hi,

    I want to do SQL server 2005 certification.
    Can anyone please provide the DUMPS for the same.

    Thanks in advance!

    Gunjan Kalra

  20. 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.

  21. 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.

  22. 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.

  23. 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.

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  25. 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

  26. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s