How to figure out what object is represented by associatedObjectId during blocking?

sql, sql-server-2012

Solution

Answer actually comes from Aaron Bertrand found it on Google Groups. To Get `OBJECT_NAME` of `associatedObjectId` you need to run the following query

SELECT OBJECT_NAME([object_id])
    FROM sys.partitions
    WHERE partition_id = 456489945132196

This number `456489945132196` represents value of `associatedObjectId` from `resource_description` column from `sys.dm_os_waiting_tasks`

Problem

I got report from another team about blocking in SQL Server. Looking at results of ``` Exec sp_who2 ``` and query from blog by Glenn Berry ``` SELECT blocking.session_id AS blocking_session_id ,blocked.session_id AS blocked_session_id ,waitstats.wait_type AS blocking_resource ,waitstats.wait_duration_ms ,waitstats.resource_description ,blocked_cache.text AS blocked_text ,blocking_cache.text AS blocking_text FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_cache CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id ``` I want not able to catch anything being blocked. Running this multiple time I start to notice that something shows up but next time I run query, blcoking is gone. I created temp table by `SELECT INTO` ``` ,blocking_cache.text AS blocking_text INTO #TempBlockingTable FROM sys.dm_exec_connections AS blocking ``` After that modified query to be `INSERT INTO SELECT`. Now I was able to run query as many times as I want without fearing that results would be gone. I kept running query for about 10 seconds until I finally got some results. ``` SELECT * FROM #TempBlockingTable ``` Looking at `resource_description` column from `sys.dm_os_waiting_tasks` I found that data is displayed in the following format. ``` <type-specific-description> id=lock<lock-hex-address> mode=<mode> associatedObjectId=<associated-obj-id> ``` Microsoft documentation on `sys.dm_os_waiting_tasks` http://technet.microsoft.com/en-us/library/ms188743.aspx does not have definition for `associatedObjectId`

Original source