T-SQL to get blocking and blocked session's SQLText

select r.wait_type , r.wait_resource, s.Login_Name, r.session_id, r.blocking_session_id, r.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, r.estimated_completion_time, CURRENT_TIMESTAMP), db_name(r.database_id) as DatabaseName, s.program_name AS ProgramName, t.[text] AS SQLText FROM master.sys.dm_exec_requests AS r INNER JOIN [master].[sys].[dm_exec_connections] conn on conn.connection_id = r.connection_id INNER JOIN [master].[sys].[dm_exec_sessions] s on s.session_id = conn.session_id CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t --CROSS APPLY master.sys.dm_exec_query_plan(r.plan_handle) AS qp WHERE r.blocking_session_id <> 0 union select br.wait_type , br.wait_resource, bs.Login_Name, br.session_id, br.blocking_session_id, br.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, br.estimated_completion_time, CURRENT_TIMESTAMP), db_name(br.database_id) as DatabaseName, bs.program_name AS ProgramName, btx.[text] AS SQLText FROM sys.dm_exec_requests AS br INNER JOIN [master].[sys].[dm_exec_connections] bconn on bconn.connection_id = br.connection_id INNER JOIN [master].[sys].[dm_exec_sessions] bs on bs.session_id = bconn.session_id CROSS APPLY sys.dm_exec_sql_text(br.[sql_handle]) AS btx --CROSS APPLY master.sys.dm_exec_query_plan(br.plan_handle) AS bqp where br.session_id in (select blkr.blocking_session_id FROM sys.dm_exec_requests AS blkr where blkr.blocking_session_id is not null) ; -----------------------------------compare to sp_who2 If you try to find active sessions, see my Active Session History presentation: ---------------------------------------------------------------------------------------------------------------------------------------- select br.request_id, br.status AS ReqStatus, bs.status AS SessStatus, bs.session_id, br.wait_type,  br.wait_time, br.wait_resource, br.command, br.cpu_time AS ReqCPUTime, bs.cpu_time AS SessCPUTime, br.total_elapsed_time, br.reads, br.writes, br.logical_reads, br.granted_query_memory, bs.memory_usage, bc.last_read, bc.last_write, bc.connect_time, bs.Login_Name, bqp.query_plan,   br.session_id, br.blocking_session_id, br.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, br.estimated_completion_time, CURRENT_TIMESTAMP), db_name(br.database_id) as DatabaseName,  bs.program_name AS ProgramName,   btx.[text] AS SQLText,       (SELECT TOP 1 SUBSTRING(btx.text,statement_start_offset / 2+1 ,       ( (CASE WHEN statement_end_offset = -1          THEN (LEN(CONVERT(nvarchar(max),btx.text)) * 2)          ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS SQLStatement, bs.[host_name]   --, cp.size_in_bytes   , bs.nt_user_name, bs.nt_domain   , bs.login_time, bs.total_scheduled_time, bs.total_elapsed_time, bs.logical_reads, bs.reads, bs.writes  FROM sys.dm_exec_sessions AS bs       INNER JOIN [master].[sys].[dm_exec_requests] br on bs.session_id = br.session_id    INNER JOIN [master].[sys].[dm_exec_connections] bc on bc.connection_id = br.connection_id  CROSS APPLY sys.dm_exec_sql_text(br.[sql_handle]) AS btx CROSS APPLY master.sys.dm_exec_query_plan(br.plan_handle) AS bqp --inner join master.sys.dm_exec_cached_plans cp on cp.plan_handle = br.plan_handle  WHERE bs.session_id <>  @@spid;  GO --------------------------------------------------------------------------------------------------------------------------------  SELECT er.request_id, er.status AS ReqStatus, es.status AS SessStatus, es.session_id, er.wait_type,  er.wait_time, er.wait_resource, er.command, er.cpu_time AS ReqCPUTime, es.cpu_time AS SessCPUTime, er.total_elapsed_time, er.reads, er.writes, er.logical_reads,  er.granted_query_memory, es.memory_usage, ec.last_read, ec.last_write, ec.connect_time, es.Login_Name, eqp.query_plan,   er.session_id, er.blocking_session_id, er.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, er.estimated_completion_time, CURRENT_TIMESTAMP), db_name(er.database_id) as DatabaseName,  es.program_name AS ProgramName,   st.[text] AS SQLText,       (SELECT TOP 1 SUBSTRING(st.text,statement_start_offset / 2+1 ,       ( (CASE WHEN statement_end_offset = -1          THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2)          ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS SQLStatement,   es.[host_name], es.nt_user_name, es.nt_domain   , es.login_time, es.total_scheduled_time, es.total_elapsed_time, es.logical_reads, es.reads, es.writes  --, cp.size_in_bytes FROM sys.dm_exec_sessions es     LEFT JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id     LEFT JOIN sys.dm_exec_requests er ON es.session_id = er.session_id     OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st     OUTER APPLY master.sys.dm_exec_query_plan(er.plan_handle) AS eqp  --inner join master.sys.dm_exec_cached_plans cp on cp.plan_handle = er.plan_handle WHERE es.session_id > 50 AND es.session_id <>  @@spid  AND es.status <> 'sleeping' AND es.status <> 'dormant'  -- < 50 system sessions --ORDER BY 1, es.cpu_time DESC ;