How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to?

Question

When I query sys.dm_tran_locks, a couple of questions arise:

I can see there are some locks that are placed at KEY, PAGE, EXTENT, RID level (resource_type column). How to find out to which table or object these particular keys, pages, extents, rids belong to?
For the OBJECT level locks (resource_type = 'OBJECT') - what is the proper way to define object name? Is the resource_associated_entity_id column the object_id of the object? Or is there some caveat?
Some OBJECTS have resource_associated_entity_id = -1261057897 (negative number) - how to find out the object name for these?

score 2 · Accepted Answer · answered Aug 15 '19 at 15:48

sys.dm_db_database_page_allocations() was introduced specifically to avoid having to do any of this spelunking yourself, using unsupported and undocumented commands like DBCC IND and DBCC PAGE. I talk about an even better replacement, coming in SQL Server 2019, in this tip. With the caveat that this is a pretty expensive query, and you may want to filter it down further to limit the output to just the specific rows in sys.dm_tran_locks you are investigating:
```
SELECT OBJECT_SCHEMA_NAME(p.object_id), OBJECT_NAME(p.object_id), * 
FROM sys.dm_tran_locks AS t
INNER JOIN sys.dm_db_database_page_allocations(DB_ID(),NULL,NULL,NULL,'LIMITED') AS p
ON t.resource_associated_entity_id = CASE 
  WHEN resource_type = N'OBJECT' THEN p.object_id
  WHEN resource_type IN (N'KEY',N'PAGE',N'RID') THEN p.rowset_id 
  -- not quite sure about EXTENT
END
WHERE OBJECTPROPERTY(p.object_id, 'IsMsShipped') = 0;
```
Yes, resource_associated_entity_id is the object_id, when the resource_type is OBJECT. For other resource types, it is going to reference other types of identifiers, like rowset_id or allocation_unit_id. This is why the column isn't simply named object_id.
SELECT OBJECT_NAME(object_id); will return the object name, regardless of whether the object_id is negative or positive. #temp tables have negative object_id values, for example, in SQL Server 2012 and up, and really there is no guarantee that any object_id will be positive.

1 Answers1