Troubleshooting locking in the database with DMV's

Anyone following my blog knows full well that most of my blog posts come from the MSDN Forums. So it should be no surprise to you if you follow my blog, that this one is yet again another question from the forums.

This one deals with locking in the database engine and how to identify what the index and object names are for locks that are taken and reported in sys.dm_tran_locks.  The resource_type column in this DMV provides information about what lock is taken, and for PAGE and RID types the resource_description column will have a page identifier like 1:2453977.  However for KEY types, the resource_description is a somewhat "cryptic" value like (52004e8d59a4). 

The question posed was how to get the index/objectid from this value.  The answer is simply, you don't/can't.  The assumption that the resource_description column is the key to the object is wrong.  The actual key to the object is the resource_associated_entity_id which is the sys.partitions DMV's hobt_id.  By joining these two views together, it is very easy to get back to the index and object since the sys.partitions DMV carries the object_id and index_id for the allocation unit being locked.  The following query demonstrates how to get the information details for a lock out of the storage engine:

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id


In researching this question, I actually learned a good bit more about object identification in SQL Server 2005 and 2008.  The sys.partitions table is actually a key table in deadlock analysis as well, which I will blog about in another entry, since deadlocks are one of my favorite subjects to