Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jan 21, 2000

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.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 )
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_hywho
GO

Create Procedure sp_hywho
(    @vcDBName sysname = NULL )
AS

SET NOCOUNT ON

IF 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 ""
END

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.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)
ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5


SET NOCOUNT OFF

GO

if exists (select * from sysobjects 
   where id = object_id('dbo.sp_hywho') 
   and sysstat & 0xf = 4)
   GRANT EXEC ON dbo.sp_hywho TO PUBLIC
GO
David Satz
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date