select
r.session_id
, r.status
, r.command
, r.database_id
, r.blocking_session_id
, r.open_transaction_count
, r.cpu_time / 1000 as cpu_time_sec
, r.total_elapsed_time / 1000 as duration_sec
, r.logical_reads / 128 as logical_reads_mb
, r.reads / 128 as physical_reads_mb
, r.writes / 128 as logical_writes_mb
, wt.*
, t.text as batch
, case when r.statement_end_offset = -1 then
substring(
t.text
, r.statement_Start_offset / 2
, 8000
)
else
substring(
t.text
, r.statement_Start_offset / 2
, (r.statement_end_offset - r.statement_start_offset) / 2
)
end as statement
, qp1.query_plan as query_plan_batch
, try_cast(qp2.query_plan as xml) as query_plan_statement
from sys.dm_exec_requests r
join sys.dm_exec_sessions s on s.session_id = r.session_id
left join sys.dm_os_waiting_tasks wt on wt.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t
outer apply sys.dm_exec_query_plan(r.plan_handle) qp1
outer apply sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) qp2
where s.is_user_process = 1
order by r.total_elapsed_time desc;