sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock to show who’s locking what and how much

Run this query, or remove the comments to actually create a stored proc.
Note that SQL pre-2008 requires a field to be commented out of the query, as noted inline.

/*
--Create Procedure WhoLock
--AS

if object_id('tempdb..#locksummary') is not null Drop table #locksummary
if object_id('tempdb..#lock') is not null Drop table #lock
create table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6))
Insert into #lock exec sp_lock
if object_id('tempdb..#who') is not null Drop table #who
create table #who (     spid int, ecid int, status char(30),
            loginame char(128), hostname char(128),
            blk char(5), dbname char(128), cmd char(16)
            --
            , request_id INT --Remove this line for SQL 2005 or earlier
            --
         )
Insert into #who exec sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'
Print '-----------------------------------------' + Char(10)
Select     left(loginame, 28) as loginame, 
    left(db_name(dbid),128) as DB,
    left(object_name(objID),30) as object,
    max(mode) as [ToLevel],
    Count(*) as [How Many],
    Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
    l.spid, hostname
into #LockSummary
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and l.status='GRANT'
group by dbID, objID, l.spid, hostname, loginame

Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object

Print '--------'
Print 'Who is blocking:'
Print '--------' + char(10)
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, 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
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Print '--------'
Print 'Details:'
Print '--------' + char(10)
Select     left(loginame, 30) as loginame,  l.spid,
    left(db_name(dbid),15) as DB,
    left(object_name(objID),40) as object,
    mode ,
    blk,
    l.status
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and blk <>0
Order by mode desc, blk, loginame, dbID, objID, l.status

Although this is a bare metal approach to investigating sql contention, the query does show you actual login names, so it enables you to join up technical issues with affected/affecting customers.

5 thoughts on “sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock to show who’s locking what and how much”

  1. This is a great example for what happens if future version of the built in sp’s differ from previous ones.

  2. I’ve been surfing on-line much more than three hours as of late, yet I in no way found any fascinating article like yours. It is actually pretty worth adequate for me. Personally, if all web site owners and bloggers created fantastic content material as you possibly did, the world wide web will most likely be significantly more beneficial than ever ahead of.

Comments are closed.

sp_WhoLock – a T-SQL stored proc c…

by Chris F Carroll read it in 2 min
5