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

2 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

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.