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

Just another day I was playing with my query which I posted earlier SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL and I found that I got error devide by zero. I have fixed this error in following query as well I have updated query to return time in millisecond instead of microsecond. Jerry Hung has also posted similar solution in comments of original article.

I strongly suggest to read original article to now more about introduction and learn about DBCC command which clears cache.

SELECT DISTINCT TOP 10
t.
TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / 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, ExecutionCount DESC
GO

Reference : Pinal Dave (https://blog.sqlauthority.com)

Best Practices, SQL Scripts, SQL Server DBCC, SQL System Table
Previous Post
SQLAuthority News – Milestone of 6 Million Visits – 60 Lak Visits – Search and Job
Next Post
SQLAuthority News – Download Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007

Related Posts

19 Comments. Leave new

  • Nice blog with huge information about SQL SERVER. I just found your blog, and really enjoyed reading this post.

    Reply
  • Hello,

    Just delete the “,” before the GO command.

    Thanks

    Reply
  • Again one easy to understand and nice blog based on SQL server….

    Reply
  • This is for 2005 but what to do for sql server 2000?
    I think we can use SQL profiler for that ,we can also check the using activity monitor,sysprocess,sp_locks , sp_who2 .

    Reply
  • Oops, don’t even know my name was mentioned, but thanks Pinal

    Just want to share, came over this article 02/20 “usp_Worst_TSQL”
    It’s a powerful script to find worst performing queries, with parameters

    Reply
  • I have multiple user databases. How would I modify the query to add a column showing which database the query is executing against?

    Reply
  • Imran Mohammed
    August 23, 2009 9:39 am

    @Wayne,

    Check This.

    SELECT DISTINCT TOP 10
    db_name(dbid) DatabaseName, — Added
    t.TEXT QueryName,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn,
    ISNULL(s.execution_count / 1000 / 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, ExecutionCount DESC

    I dont know why database name is returned NULL some times… Check this throughly before you use it…

    ~ IM.

    Reply
  • Hi ,

    Is there any method to get the host name with this query?
    I am not able to connect this with sysprocesses to get the hostname.
    Please help.

    Reply
  • Hi,

    Please add the hostname with this query.
    I am not able to join sysprocess for hostname with this query.

    Reply
  • I guess this query will also return the long running query. You can also add the filters to see the long running transactions.

    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
    WHERE A.dbid > 4 —-to filter system databases
    ORDER BY RunningTime DESC
    GO

    Reply
  • Hi Pinal,

    we are getting “insufficient system memory to run this query” error. It’s a SQL 2k5 sp2 on Intel E5520 series machine. Please advise on this error…

    Reply
  • Hi Pinal,

    I think you don’t need to devide by 1000 for FrequencyPerSec

    Reply
  • My understanding is that sys.dm_exec_query_stats is for archived query statistics. I’ve been using the following to monitor active long running queries and it works fine for single statements – however it seems to return hugely inflated total_elapsed_time values with some procs or functions where multiple statements are run. I’m also interested if anyone know how to find the query completion times from DMVs?

    THANKS

    select …..
    FROM sys.dm_exec_requests er
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
    INNER JOIN sys.dm_exec_sessions AS es
    ON es.session_id = er.session_id
    INNER JOIN sys.dm_exec_connections AS ec
    ON ec.session_id = es.session_id
    where er.total_elapsed_time / 1000.0 > @ElapsedThreshold
    ORDER BY er.total_elapsed_time DESC;

    Reply
  • @Imran,
    in SQL Reference said: “dbid – smallint – ID of database. Is NULL for ad hoc and prepared SQL statements.”
    Meanwhile, there is an example in REF: “SELECT TOP 5 creation_time, last_execution_time, …” – nearly the same as above.

    Reply
  • I get
    Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ‘.’.

    on this line: CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

    When running on SQL2005 servers that are hosting SQL2000 compatible DB’s.

    Is there a way to resolve that?

    Reply
  • Change (s.sql_handle) to (sql_handle)

    Reply
  • Hi,

    Tried the above query but i got the same error message as David Clary. I tried Sahun’s suggestion but got this error message instead.

    “SQL_ handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure your database compatibility mode is set to 90.

    Any help is appreciated.

    thanks

    Reply
  • Since I was interested in only actual data and not what happened a month ago i inserted a date filter. Also my stored procedures names did not appear (column QueryName was null). So here my script version.
    For the initial script I give three virtual footkisses to Dave and one for Mohammed.

    DECLARE @Now DATETIME
    SET @Now = GETDATE()
    SET @Now = DATEADD(DAY, -1, @Now)
    SELECT DISTINCT TOP 10
    db_name(t.dbid) DatabaseName,
    OBJECT_NAME(t.objectid) AS ObjectName,
    t.TEXT QueryName,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn,
    ISNULL(s.execution_count / 1000 / 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
    WHERE s.creation_time > @Now
    ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC

    Regards
    BenS

    Reply
  • Christo Pretorius
    October 3, 2012 1:12 pm

    The following code has been optimized to spool to a text output (I use it in an email).

    SELECT
    ‘SESSION_ID: ‘ + CAST(es.[session_id] AS VARCHAR(4)) + ‘ ‘ +
    ‘ HOST_NAME: ‘ + es.[host_name] + ‘ ‘ +
    ‘ PROGRAM_NAME: ‘ + es.[program_name], ‘ ‘ + CHAR(13) + CHAR(10),
    ‘ LOGIN_NAME: ‘ + es.[login_name] + ‘ ‘ +
    ‘ PROCESS_OWNER: ‘ + CASE es.[is_user_process]
    WHEN 1 THEN ‘User’
    ELSE ‘System’ END, ‘ ‘ + CHAR(13) + CHAR(10),
    ‘ TRANSACTION_START_TIME: ‘ + CAST(tat.[transaction_begin_time] AS VARCHAR) + ‘ ‘ +
    ‘ LAST_READ_TIME: ‘ + CAST(ec.[last_read] AS VARCHAR) + ‘ ‘ +
    ‘ LAST_WRITE_TIME: ‘ + CAST(ec.[last_write] AS VARCHAR) + ‘ ‘ +
    ‘ SESSION_STATUS: ‘ + es.[status], ‘ ‘ + CHAR(13) + CHAR(10),
    ‘ TRANSACTION_STATE: ‘ + CASE tat.[transaction_state]
    WHEN 0 THEN ‘The transaction has not been completely initialized yet.’
    WHEN 1 THEN ‘The transaction has been initialized but has not started.’
    WHEN 2 THEN ‘The transaction is active.’
    WHEN 3 THEN ‘The transaction has ended. This is used for read-only transactions.’
    WHEN 4 THEN ‘The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.’
    WHEN 5 THEN ‘The transaction is in a prepared state and waiting resolution.’
    WHEN 6 THEN ‘The transaction has been committed.’
    WHEN 7 THEN ‘The transaction is being rolled back.’
    WHEN 8 THEN ‘The transaction has been rolled back.’ END + ‘ ‘ +
    ‘ TRANSACTION_TYPE: ‘ + CASE CAST(tat.[transaction_type] AS VARCHAR)
    WHEN ‘1’ THEN ‘Read/Write’
    WHEN ‘2’ THEN ‘Read-only’
    WHEN ‘3’ THEN ‘System’ END , ‘ ‘ + CHAR(13) + CHAR(10),
    ‘ SQL_TEXT: ‘ + est.text, ‘ ‘ + CHAR(13) + CHAR(10) + ‘ ‘ + CHAR(13) + CHAR(10)
    FROM sys.dm_tran_active_transactions tat
    INNER JOIN sys.dm_tran_session_transactions tst
    ON tst.transaction_id = tat.transaction_id
    INNER JOIN sys.dm_exec_sessions es
    ON es.session_id = tst.session_id
    INNER JOIN sys.dm_exec_connections ec
    ON ec.session_id = es.session_id
    OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) est
    ORDER BY tat.[transaction_begin_time] ASC;

    Reply

Leave a Reply