5 Longest Running Queries.

Syntax

Lists the t-sql statements of the 5 longest running queries


        SELECT TOP 5
        DatabaseName = DB_NAME(QT.dbid)
        ,ObjectName = O.name
        ,AvgSec = QS.total_elapsed_time / QS.execution_count / 1000000.0 AS average_seconds
        ,TotalSec = QS.total_elapsed_time / 1000000.0
        ,QS.execution_count
        ,QryStmt =
          SUBSTRING (QT.text,QS.statement_start_offset/2,
          (
            CASE
              WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), QT.text)) * 2
              ELSE QS.statement_end_offset
            END - QS.statement_start_offset)/2
          )
        FROM
        [master].sys.dm_exec_query_stats QS (NOLOCK)
        CROSS APPLY
        [master].sys.dm_exec_sql_text(QS.sql_handle) QT
        LEFT JOIN
        sys.objects O
          ON QT.objectid = O.object_id
        WHERE
        QT.dbid = DB_ID()
        ORDER BY
        AvgSec DESC OPTION (RECOMPILE)
        ;