Sp block--check analyse blocked processes

From SybaseWiki
Jump to: navigation, search

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.
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.

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