I'm trying to understand/learn how to track down the details of a blocked session.

So I created the following setup:

create table foo (id integer not null primary key, some_data varchar(20));
insert into foo values (1, 'foo');

Now I connect to the database twice from two different clients.

The first session issues:

begin transaction
update foo set some_data = 'update'
  where id = 1;

I explicitly do not commit there in order to keep the locks.

In the second session I issue the same statement and of course that one waits due to locking. Now I'm trying to use the different queries floating around in order to see that session 2 is waiting for the foo table.

sp_who2 shows the following (I removed some columns to only show the important information):

SPID | Status       | BlkBy | DBName   | Command          | SPID | REQUESTID
52   | sleeping     |   .   | foodb    | AWAITING COMMAND | 52   | 0        
53   | sleeping     |   .   | foodb    | AWAITING COMMAND | 53   | 0        
54   | SUSPENDED    | 52    | foodb    | UPDATE           | 54   | 0        
56   | RUNNABLE     |   .   | foodb    | SELECT INTO      | 56   | 0        

This is expected, session 54 is blocked by the un-committed changes from session 52.

Querying sys.dm_os_waiting_tasks also shows this. The statement:

select session_id, wait_type, resource_address, resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not null;


session_id | wait_type | resource_address   | resource_description                                                            
        54 | LCK_M_X   | 0x000000002a35cd40 | keylock hobtid=72057594046054400 dbid=6 id=lock4ed1dd780 mode=X associatedObjectId=72057594046054400

Again this is expected.

My problem is, that I can't figure out how to find the actual object name that session 54 is waiting for.

I have found several queries that are joining sys.dm_tran_locks and sys.dm_os_waiting_tasks like this:

FROM sys.dm_tran_locks AS l
  JOIN sys.dm_os_waiting_tasks AS wt ON wt.resource_address = l.lock_owner_address

But in my above test scenario this join does not return anything. So either that join is wrong or dm_tran_locks doesn't actually contain the information I'm looking for.

So what I am looking for is a query that returns something like:
"session 54 is waiting for a lock in table foo".

Some background info:

The real life problem I'm trying to solve is a bit more complicated, but boils down to the question "on which table is session 54 waiting for". The problem in question involves a largish stored procedure that updates several tables and a select from a view that accesses some of those tables. The select statement is blocked even though we have snapshot isolation and read committed snapshot enabled. Figuring out why the select is blocked (which I thought would not be possible if snapshot isolation is enabled) will be the next step.

As a first step I'd like to find out on what that session is waiting.

  • msdn.microsoft.com/en-us/library/ms190345.aspx says your join is correct.
    – Hannah Vernon
    Oct 8, 2014 at 13:47
  • @MaxVernon: thanks for confirming that. But then I'm even more confused. Why does it not return anything although I know there is a lock and a blocked session?
    – user1822
    Oct 8, 2014 at 13:51
  • I'm unable to recreate the problem you are seeing in SQL Server 2012. I created a test database, enabled RCSI, created your tables, and ran both update statements, and I see a row returned by your last query.
    – Hannah Vernon
    Oct 8, 2014 at 13:54
  • If you want a visual aid in detecting your locks there is an open source tool available called SQL lock finder. You can find the source on: github.com/LucBos/SqlLockFinder Or download the executable on: sqllockfinder.com We also love any contributions you could make to the code so we can make it better.
    – Luc Bos
    Nov 6, 2019 at 6:26

2 Answers 2


I think this does what you need.

USE 'yourDB'
    OBJECT_NAME(p.[object_id]) BlockedObject
FROM    sys.dm_exec_connections AS blocking
    INNER JOIN sys.dm_exec_requests blocked
        ON blocking.session_id = blocked.blocking_session_id
    INNER JOIN sys.dm_os_waiting_tasks waitstats
        ON waitstats.session_id = blocked.session_id
    INNER JOIN sys.partitions p ON SUBSTRING(resource_description, 
        PATINDEX('%associatedObjectId%', resource_description) + 19, 
        LEN(resource_description)) = p.partition_id

You can try it :

db_name(rsc_dbid) AS 'DATABASE_NAME',
case rsc_type when 1 then 'null'
              when 2 then 'DATABASE' 
              WHEN 3 THEN 'FILE'
              WHEN 4 THEN 'INDEX'
              WHEN 5 THEN 'TABLE'
              WHEN 6 THEN 'PAGE'
              WHEN 7 THEN 'KEY'
              WHEN 8 THEN 'EXTEND'
              WHEN 9 THEN 'RID ( ROW ID)'
              WHEN 10 THEN 'APPLICATION' end  AS 'REQUEST_TYPE',

                   WHEN 2 THEN 'CURSOR'
                   WHEN 3 THEN 'SESSION'

OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME', 
PROCESS.program_name , 
PROCESS.nt_domain , 
PROCESS.nt_username , 
PROCESS.program_name ,
FROM sys.syslockinfo LOCK JOIN 
     sys.sysprocesses PROCESS
  ON LOCK.req_spid = PROCESS.spid

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.