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

21 thoughts on “SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  9. 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?

    Like

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

    Like

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

    Like

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

    Like

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

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

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