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