This SQL query retrieves information about the top 10 currently executing sessions along with details about their associated queries. It uses the sys.dm_exec_sessions
and sys.dm_exec_requests
dynamic management views to gather information about sessions and their corresponding requests.
SELECT TOP 10 s.session_id,
r.status,
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE r.statement_end_offset
END …