SELECT r.session_id ,r.blocking_session_id ,DB_Name(r.database_id) as database_name ,r.start_time ,r.total_elapsed_time ,r.[status] ,CASE WHEN r.blocking_session_id <> 0 THEN 'Blocking' WHEN r.blocking_session_id = 0 AND r.wait_type is not null THEN 'Waiting' ELSE 'Long-running' END as slowness_type ,r.percent_complete ,r.command ,r.wait_type ,r.wait_time ,r.wait_resource ,r.last_wait_type ,r.cpu_time ,r.reads ,r.writes ,r.logical_reads ,t.[text] as executing_batch ,SUBSTRING(t.[text], r.statement_start_offset/2, (CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) --LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset )/2 + 1) as executing_sql ,bt.[text] as blocking_batch ,SUBSTRING(bt.[text], br.statement_start_offset/2, (CASE WHEN br.statement_end_offset = -1 THEN DATALENGTH (bt.[text]) --LEN(CONVERT(NVARCHAR(MAX), bt.text)) * 2 ELSE br.statement_end_offset END - br.statement_start_offset )/2 + 1) as blocking_sql --,p.query_plan FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) as p LEFT JOIN sys.dm_exec_requests br ON r.blocking_session_id = br.session_id OUTER APPLY sys.dm_exec_sql_text(br.session_id) as bt WHERE r.session_id > 50 and r.session_id <> @@SPID AND r.total_elapsed_time > 30 * 60 * 1000 ORDER BY r.total_elapsed_time DESC;