-- Check for Blocking
-- lists the blocker and waiter sql statements

select getdate() as 'logtime',
	es.session_id, es.last_request_start_time, es.last_request_end_time, 
	qr.status as 'Request Status',
	es.status as 'Session Status', qr.blocking_session_id, 
	case qr.transaction_isolation_level 
		when 0 then 'Unspecified'
		when 1 then 'ReadUncommitted'
		when 2 then 'ReadCommitted'
		when 3 then 'Repeatable'
		when 4 then 'Serializable'
		when 5 then 'Snapshot'
	end as 'Request Isolation Level',
	case es.transaction_isolation_level 
		when 0 then 'Unspecified'
		when 1 then 'ReadUncommitted'
		when 2 then 'ReadCommitted'
		when 3 then 'Repeatable'
		when 4 then 'Serializable'
		when 5 then 'Snapshot'
	end as 'Session Isolation Level',
	qr.wait_type, qr.wait_time, qr.wait_resource,
	qr.open_transaction_count, qr.transaction_id, qtran.transaction_begin_time, 
	case qtran.transaction_type
		when 1 then 'Read/Write'
		when 2 then 'Read-only'
		when 3 then 'System'
		when 4 then 'Distributed'
	end as 'Transaction Type',
	qr.cpu_time, qr.total_elapsed_time, qr.reads, qr.writes, 
	qr.logical_reads,
	SUBSTRING(qt.text,qr.statement_start_offset/2, 
			(case when qr.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else qr.statement_end_offset end -qr.statement_start_offset)/2) 
		as request_query_text,
	(select text from sys.dm_exec_connections as p		--- get sql for blocker
		cross apply sys.dm_exec_sql_text(p.most_recent_sql_handle) where p.session_id = qr.blocking_session_id) as blocker_query_text
from sys.dm_exec_sessions es
left outer join sys.dm_exec_requests qr on es.session_id = qr.session_id
	outer apply sys.dm_exec_sql_text(qr.sql_handle) as qt
left outer join sys.dm_tran_active_transactions qtran on qr.transaction_id = qtran.transaction_id
where 
	es.is_user_process = 1
order by qr.total_elapsed_time desc