Retrieve Top 10 Longest Running Queries in SQL Server

This query fetches the top 10 longest-running queries based on total elapsed time from the SQL Server dynamic management views (DMVs). It provides details such as the SQL handle, total execution time, execution count, and the actual query text.

SELECT TOP 10
    qs.sql_handle,
    qs.total_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
            ELSE qs.statement_end_offset END) - qs.statement_start_offset) / 2 + 1
    ) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;

• The sys.dm_exec_query_stats DMV stores performance statistics of cached query plans.
• The sys.dm_exec_sql_text function retrieves the SQL text associated with a specific query plan.
• The query orders results in descending order by total elapsed time, helping identify slow-performing queries for optimization.