Detecting the locked table or row in SQL Server

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');
commit;

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;

returns:

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.

I think this does what you need.

USE 'yourDB'
GO
SELECT  
    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

StackExchange Database Administrators Q#78683, answer score: 24