pointers, solutions and scripts for the SQL DBA
not intended to replace common sense

4/19/2012

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

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

I've added the following features:
  • The query execution plan of the active request in the QueryPlan column to the right.
  • Variable at the top which changes the relationship of the join between sys.dm_exec_sessions and  sys.dm_exec_requests. 
    • When set to 0, this query now displays all sessions, even those without active requests.  I recently found this helpful when researching sleeping sessions that were blocking active sessions.
    • When set to 1, this query displays as it used to - only session and active request data.  
  • Percent_Complete column - great for finding the progress of backup and restores 
  • A few other minor helpful columns
Update March 4, 2014:
  • Added Transaction Isolation Level for Session and Request
  • Added Status for Session and Request
  • Added Login_name, Client_Interface_name

declare @showallspids bit 
select @showallspids = 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
, login_name nvarchar(256) null
, client_interface_name nvarchar(64)
, session_status nvarchar(60) null
, request_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
, last_wait_type 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
, session_transaction_isolation_level varchar(20) null
, request_transaction_isolation_level varchar(20) null
)

insert into #ExecRequests (session_id,request_id, request_start_time, login_time, login_name, client_interface_name, session_status, request_status, command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,blocking_session_id,wait_type,last_wait_type,wait_time_s,wait_resource,cpu_time_s,tot_time_s,reads,writes,logical_reads,[host_name], [program_name] , session_transaction_isolation_level , request_transaction_isolation_level )
      select s.session_id,request_id, r.start_time, s.login_time, s.login_name, s.client_interface_name, s.status, r.status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,r.database_id,user_id,blocking_session_id,wait_type,r.last_wait_type, r.wait_time/1000.,r.wait_resource ,r.cpu_time/1000.,r.total_elapsed_time/1000.,r.reads,r.writes,r.logical_reads,s.[host_name], s.[program_name], s.transaction_isolation_level, r.transaction_isolation_level
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 s.session_id >= 50 --retrieve only user spids
and s.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.session_status
 , r.request_status
 , r.blocking_these
 , blocked_by =  r.blocking_session_id
 , r.wait_type 
 , r.wait_resource
 , r.last_wait_type
 , DBName = db_name(r.database_id)
 , r.command
 , login_time
 , login_name
 , client_interface_name
 , 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 
 , request_transaction_isolation_level = case request_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 
 , session_transaction_isolation_level = case session_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 

 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

3 comments:

  1. Msg 102, Level 15, State 1, Line 101
    Incorrect syntax near '.'.

    ReplyDelete
  2. Anonymous-
    Look out for SQL 2000 compatibility databases or SQL 2000 instances. I can reproduce your error only by running the script in the context of a database in SQL database in Compatibility Level 80.
    This script, like any script that uses a DMV, won't work for SQL 2000.

    ReplyDelete
  3. Thanks for the post - very helpful!

    ReplyDelete

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.