SQL Server 2017 – How to Find Table/Object for KEY, PAGE, EXTENT Locks

lockingsql serversql-server-2017

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

  1. 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?

  2. 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?

  3. Some OBJECTS have resource_associated_entity_id = -1261057897 (negative number) – how to find out the object name for these?

Best Answer

  1. 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;

  2. 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.

  3. 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.
Related Question