http://www.sqlservercentral.com/blogs/sqltact/2012/04/19/using-sysdm_exec_requests-to-find-a-blocking-chain-and-much-more/

Printed 2014/04/20 08:23AM

Using sys.dm_exec_requests to find a blocking chain and much more

2012/04/19

As with every good DBA's toolbox, my blocking list query using sys.dm_exec_requests is evolving.

I've added the following features:




declare @showallspids bit = 1

create table #ExecRequests  (
 id int IDENTITY(1,1) PRIMARY KEY
, session_id smallint not null
, request_id int null
, request_start_time datetime null
, login_time datetime not null
, status nvarchar(60) null
, command nvarchar(32) null
, sql_handle varbinary(64) null
, statement_start_offset int null
, statement_end_offset int null
, plan_handle varbinary (64) null
, database_id smallint null
, user_id int null
, blocking_session_id smallint null
, wait_type nvarchar (120) null
, wait_time_s int null
, wait_resource nvarchar(120) null
, cpu_time_s int null
, tot_time_s int null
, reads bigint null
, writes bigint null
, logical_reads bigint null
, [host_name] nvarchar(256) null
, [program_name] nvarchar(256) null
, blocking_these varchar(1000) NULL
, percent_complete int null
)

insert into #ExecRequests (session_id,request_id, request_start_time, login_time, status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,blocking_session_id,wait_type,wait_time_s,wait_resource,cpu_time_s,tot_time_s,reads,writes,logical_reads,[host_name], [program_name] )
      select s.session_id,request_id, r.start_time, s.login_time, r.status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,s.database_id,user_id,blocking_session_id,wait_type,r.wait_time/60.,r.wait_resource ,r.cpu_time/60.,r.total_elapsed_time/60.,r.reads,r.writes,r.logical_reads,s.[host_name], s.[program_name]
from sys.dm_exec_sessions s
left outer join sys.dm_exec_requests r 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
and  (@showallspids = 1 or r.session_id is not null)

update #ExecRequests
set blocking_these = LEFT((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
      FOR XML PATH('')
      ),1000)
select * from
(
 select 
   r.session_id , r.host_name , r.program_name
 , r.status
 , r.blocking_these
 , blocked_by =  r.blocking_session_id
 , r.wait_type , r.wait_resource
 , DBName = db_name(r.database_id)
 , r.command
 , login_time
 , request_start_time
 , r.tot_time_s, r.wait_time_s, r.cpu_time_s, r.reads, r.writes, r.logical_reads
 --, [fulltext] = est.[text]
 , offsettext = CASE WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN NULL
        ELSE 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
             END )
      END
 , r.statement_start_offset, r.statement_end_offset
 , cacheobjtype = LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35)
 , QueryPlan  = qp.query_plan
 from #ExecRequests r
 LEFT OUTER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = r.plan_handle
 OUTER APPLY sys.dm_exec_query_plan (r.plan_handle) qp
 OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) est
) a
order by LEN(blocking_these) desc, blocking_these desc, blocked_by desc, session_id asc

drop table #ExecRequests 

GO




Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.