SELECT
r.session_id AS blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
r.blocking_session_id,
s.login_name,
s.host_name,
s.program_name,
s.client_interface_name,
s.status,
s.cpu_time,
s.memory_usage,
s.total_elapsed_time,
s.reads,
s.writes,
st.text AS current_statement,
qp.query_plan
FROM
sys.dm_exec_requests r
INNER JOIN
sys.dm_exec_sessions s ON r.session_id = s.session_id
OUTER APPLY
sys.dm_exec_sql_text(r.sql_handle) AS st
OUTER APPLY
sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE
r.blocking_session_id <> 0
ORDER BY
r.wait_time DESC;
|