devxlogo

See Who Is Blocking Your SQL Server

See Who Is Blocking Your SQL Server

If you have ever monitored any blocking problems in SQL Server, you know that sp_who only shows you the spid (SQL Server’s internal Process ID) that is causing the blocking for each spid that is blocked. Often a blocked spid is shown as causing blocking for another spid. To see the spid (or spids) that started the whole mess off, execute the following SQL:

  SELECT p.spid,convert(char(12), d.name) db_name, program_name, convert(char(12), l.name) login_name, convert(char(12), hostname) hostname, cmd, p.status, p.blocked, login_time, last_batch, p.spidFROM      master..sysprocesses pJOIN      master..sysdatabases d ON p.dbid =  d.dbidJOIN      master..syslogins l ON p.suid = l.suidWHERE     p.blocked = 0AND       EXISTS (  SELECT 1          FROM      master..sysprocesses p2          WHERE     p2.blocked = p.spid )

We built this into our own version of sp_who, called sp_hywho. See the listing below.

Code for sp_hywho:

 if exists (select * from sysobjects    where id = object_id('dbo.sp_hywho')    and sysstat & 0xf = 4)   drop procedure dbo.sp_hywhoGOCreate Procedure sp_hywho(    @vcDBName sysname = NULL )ASSET NOCOUNT ONIF EXISTS ( SELECT 1   FROM   master..sysprocesses p   WHERE   p.blocked = 0    AND   EXISTS ( SELECT 1      FROM   master..sysprocesses p2      WHERE   p2.blocked = p.spid ) )BEGIN   PRINT "Blocking caused by:"   PRINT ""   SELECT   p.spid   ,convert(char(12), d.name) db_name   , program_name   , convert(char(12), l.name) login_name   , convert(char(12), hostname) hostname   , cmd              , p.status           , p.blocked   , login_time                     , last_batch    , p.spid   FROM   master..sysprocesses p   JOIN   master..sysdatabases d ON p.dbid =  d.dbid    JOIN   master..syslogins l ON p.suid = l.suid    WHERE   p.blocked = 0    AND   EXISTS ( SELECT 1      FROM   master..sysprocesses p2      WHERE   p2.blocked = p.spid )    AND   (p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)   ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5   PRINT ""ENDSELECT   p.spid,convert(char(12), d.name) db_name, program_name, convert(char(12), l.name) login_name, convert(char(12), hostname) hostname, cmd           , p.status        , p.blocked, login_time                  , last_batch , p.spidFROM   master..sysprocesses pJOIN   master..sysdatabases d ON p.dbid =  d.dbid JOIN   master..syslogins l ON p.suid = l.suid WHERE   (p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5SET NOCOUNT OFFGOif exists (select * from sysobjects    where id = object_id('dbo.sp_hywho')    and sysstat & 0xf = 4)   GRANT EXEC ON dbo.sp_hywho TO PUBLICGO

devx-admin

Share the Post: