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?

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.