Top 10 Longest-Running Queries

A query that lists the ten longest-running queries currently executing with their statement text.

Published Feb 19, 2006

What is taking the longest right now? This query pulls the top ten currently executing requests by total elapsed time and extracts just the active statement from each batch - the fastest possible answer to "what is running?"

SELECT
	TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1, ((CASE statement_end_offset
	WHEN -1
	THEN DATALENGTH(text) 
	ELSE statement_end_offset
	END - statement_start_offset)/2) + 1) AS query_text,
	* 
FROM
	sys.dm_exec_requests
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
ORDER BY
	total_elapsed_time DESC