Bobh: Initial version.

2009-02-23T12:17:30Z

Initial version.

New page

This sp checks for blocked processes and, if found, returns the spid info, sql text and query plan. It requires sp__mon_sql2 and sp__capture_sql.<br>
It could do with some extra work to filter out the spids which are not the root blocking spids; it can produce a large amount of output if there are a high number of individual spids involved in a chain of blocks.
<pre>
create proc sp__block
as
------------------------------
-- Procedure: sp__block
-- Created: April 2008
-- Author: Bob Holmes
-- Last modification:
-- Dependencies: sp__mon_sql2, sp__capture_sql
------------------------------
declare @numblocks int
select @numblocks = count(*) from master..sysprocesses where blocked <> 0
-- list blocked processes
print " %1! Blocked Processes", @numblocks
print " =============================================================================================="
print " duration"
select spid,
convert(char(12),suser_name(suid)) login,
convert(char(16),db_name(dbid)) db,
hostname,
blocked "blocked by",
convert(varchar(9),StartTime,108) StartTime,
right( replicate("0", 2) + convert(varchar(2),(time_blocked/60/60)),2)
+ ":"
+right( replicate("0", 2) + convert(varchar(2),((time_blocked/60)-((time_blocked/60/60)*60))),2)
+ ":"
+ right( replicate("0", 2) + convert(varchar(2),(time_blocked%60)),2) "hh:mm:ss",
cmd Command
from master..sysprocesses, master..monProcessStatement
where blocked <> 0
and spid = SPID

if @numblocks <> 0
begin
--prep list of blocking spids
select distinct blocked into #blockingspids from master..sysprocesses where blocked <> 0
declare @spid int
--get spid info: summary, sql, query plan
select @spid = min(blocked) from #blockingspids
while @spid >= (select min(blocked) from #blockingspids)
begin
print ""
exec sp__mon_sql2 @spid
exec sp__capture_sql @spid
exec sp_showplan @spid, null, null, null
print "Tables in use by Spid %1!", @spid
print "-----------------------------"
select
a.SPID,
convert(char(12),a.Login) Login,
convert(char(15),a.ClientHost),
convert(char(15),a.ClientIP) ClientIP,
convert(char(15),b.DBName) DBName,
b.ObjectName,
c.LogicalReads,
c.PhysicalReads,
c.PhysicalWrites,
b.TableSize
from master..monProcessLookup a, master..monProcessObject b, master..monProcessActivity c
where a.SPID = b.SPID
and b.SPID = c.SPID
and a.SPID = @spid
order by c.PhysicalWrites desc

if (@spid = (select max(blocked) from #blockingspids)) or (@spid = null)
break
select @spid = min(blocked) from #blockingspids where blocked > @spid
end
end
return
</pre>
[[Category:ASE]]

Sp block--check analyse blocked processes - Revision history

Bobh: Initial version.