Longest running queries/processes in SQL.

  • Hi Guys,

    I need a script to identify the longest running queries/processes in SQL.

    Thanks in advance 🙂

  • There are plenty of examples online. Just Googling for the phrase you posted "Find the longest running queries/processes in SQL" yielded lots of relevant results.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Here's a similar one to the one linked directly above, but that also brings in the showplan XML. A little "heavier" query, but very useful if used sparingly.

    SELECT TOP 200

    sql.text as sql

    , qp.query_plan

    , qs.plan_handle

    , creation_time

    , last_execution_time

    , execution_count

    , (total_worker_time / execution_count) as avg_cpu_usec

    , total_worker_time as total_cpu_usec

    , (total_elapsed_time/execution_count) as avg_duration_usec

    , last_worker_time as last_cpu_usec

    , min_worker_time as min_cpu_usec

    , max_worker_time as max_cpu_usec

    , (total_physical_reads + total_logical_reads) as total_reads

    , (max_physical_reads + max_logical_reads) as max_reads

    , (total_physical_reads + total_logical_reads) / execution_count as avg_reads

    , max_elapsed_time as max_duration_usec

    , total_elapsed_time as total_duration_usec

    , sql.dbid

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql

    CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp

    where isnull(sql.dbid,0) <> 32767 and sql.text not like 'select top%sql.text%'

    ORDER BY qs. total_worker_time desc, avg_duration_usec DESC

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply