SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using sys.dm_exec_requests to find a blocking chain

A query that calls sys.dm_exec_requests and pulls the statement text out of the request is a handy script for any DBA's toolbox.  A simple version of the query performs a cross apply to dm_exec_sql_text and pulls out the request's TSQL statement-in-progress using the unicode character offsets.

This is simple enough and does a good job as a queryable sp_who2 and DBCC INPUTBUFFER replacement to an extent.

In an environment that suffers from frequent spells of query blocking, the ability to identify which query was leading the way becomes important in finding the query or queries to troubleshoot.  Whether the end result of his query was a simple KILL command, and/or a walk down the hall to investigate the "business case" would be a situational decision.

I wanted a blocking chain - a list of all other spids that a session was blocking.

Here's a query that dumps the output of dm_exec_requests into a temporary table, then uses the XML PATH to conveniently transform the blocked_by list into a comma-delimited list in the row of the blocker.  The  sort order is the length of this comma-delimited list, putting the cause of a pileup right at the top.

Doing this in a temp table was necessary because the blocking_session_id is a highly volatile column.  As a result, the output of this query is several ms behind "live".  After a few executions, the spid hanging out at the top is easy enough to spot.

create table #ExecRequests (
,session_id smallint not null
,request_id int
,start_time datetime
,status nvarchar(60)
,command nvarchar(32)
,sql_handle varbinary(64)
,statement_start_offset int
,statement_end_offset int
,plan_handle varbinary (64)
,database_id smallint
,user_id int
,blocking_session_id smallint
,wait_type nvarchar (120)
,wait_time int
,cpu_time int
,tot_time int
,reads bigint
,writes bigint
,logical_reads bigint
,[host_name] nvarchar(256)
,[program_name] nvarchar(256)
,blocking_these varchar(1000) null

insert into #ExecRequests (session_id,request_id, start_time,status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,blocking_session_id,wait_type,wait_time,cpu_time,tot_time,reads,writes,logical_reads,host_name, program_name)
select r.session_id,request_id, start_time,r.status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,r.database_id,user_id,blocking_session_id,wait_type,wait_time,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads,s.host_name, s.program_name
from sys.dm_exec_requests r
left outer join sys.dm_exec_sessions s on r.session_id = s.session_id
where 1=1
and r.session_id > 35 --retrieve only user spids
and r.session_id <> @@SPID --ignore myself

update #ExecRequests set blocking_these = (select isnull(convert(varchar(5), er.session_id),'') + ', '
from #ExecRequests er
where er.blocking_session_id = isnull(#ExecRequests.session_id ,0)
and er.blocking_session_id <> 0

r.session_id, r.host_name, r.program_name, r.status
, r.blocking_these
, 'LEN(Blocking)' = LEN(r.blocking_these)
, blocked_by = r.blocking_session_id
, r.tot_time
, DBName = db_name(r.database_id), r.command, r.wait_type, r.tot_time, r.wait_time, r.cpu_time, r.reads, r.writes, r.logical_reads
, [text] = est.[text]
, offsettext = CASE WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN null
SUBSTRING (est.[text], r.statement_start_offset/2 + 1,
CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text]))
ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
, r.statement_start_offset, r.statement_end_offset
from #ExecRequests r
outer apply sys.dm_exec_sql_text (r.sql_handle) est
order by LEN(r.blocking_these) desc, r.session_id asc
drop table #ExecRequests


Posted by Anonymous on 19 October 2011

Pingback from  Dew Drop &ndash; October 19, 2011 | Alvin Ashcraft&#039;s Morning Dew

Posted by Jason Brimhall on 25 October 2011

nice script - thanks for sharing.

Posted by Bob Alford on 25 October 2011

Thanks for the query.  When I try it on 2008 R2, I get:

Msg 102, Level 15, State 1, Line 62

Incorrect syntax near '.'

Looks like it is having trouble with:

outer apply sys.dm_exec_sql_text (r.sql_handle) est

Posted by william_a_dba on 25 October 2011


Msg 102, Level 15, State 1, Line 62

Incorrect syntax near '.'.

I can only reproduce this error when running the script in a database that is in SQL 2000 compatibility mode.  Can you confirm the compatibility mode of the current database, or try a different database?

Posted by Bob Alford on 25 October 2011

Figured it out.  Have to be connected to Master.  Thanks!

Posted by Bob Alford on 25 October 2011

Thanks!  The compatibility mode was 2000 on that database.

Posted by william_a_dba on 25 October 2011

Thanks for asking!

Posted by Anonymous on 25 October 2011

Pingback from  Using sys.dm_exec_requests to find a blocking... | SQL Server | Syngu

Leave a Comment

Please register or log in to leave a comment.