Currently Executing Requests

A detailed view of every request currently executing on the server: session ID, thread count, database, domain and user, host, status, wait type, CPU, reads (physical and logical), writes, duration, the exact statement being executed right now, its parent batch, the program name and start time. The workhorse query for "what is the server doing this second?"

SELECT
	er.session_Id as [SPID],
	COUNT(DISTINCT sp.ecid) as [Threads],
	DB_NAME(MAX(sp.[dbid])) as [Database],
	MAX(sp.nt_domain) as [Domain],
	MAX(nt_username) as [User],
	MAX(sp.hostname) as [Host],
	MAX(er.[status]) as [Status],
	MAX(er.wait_type) as [Wait],
	MAX(er.cpu_time) as [CPU],
	MAX(er.reads) as [Reads],
	MAX(er.logical_reads) as [LogicalReads],
	MAX(er.writes) as [Writes],
	MAX(er.total_elapsed_time) as [Duration],
	MAX(SUBSTRING (qt.[text],
		er.statement_start_offset/2,
			(CASE WHEN er.statement_end_offset = -1
		THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
		ELSE er.statement_end_offset END - 
		er.statement_start_offset)/2)) as [Query],
	MAX(qt.[text]) as [ParentQuery],
	MAX(sp.[program_name]) as [Program],
	MAX(er.start_time) as [StartTime]
FROM
	sys.dm_exec_requests as er
INNER JOIN sys.sysprocesses as sp
	ON er.session_id = sp.spid
	CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE
	er.[session_id] > 50
	AND er.[session_id] NOT IN (@@SPID)
GROUP BY
	er.[session_id]
ORDER BY
	er.[session_id]