20 December,2012 by Jack Vamvas
I’ve been troubleshooting some Locks problems recently, where a transaction is taking Exclusive Locks for a long period, causing severe blocking for other user queries and causing application timeouts
I’ve been using this query to report on the current Lock manager resources. The query utilises a number of DMVs to supply extra information , such as blocking session and object names.
The Sys.dm_tran_locks DMV reports on the active lock manager resources. This joins via the lock_owner_address to the sys.dm_os_waiting_tasks , which returns tasks waiting on a resource.
There are also three other joins 1) sys.objects 2) sys.partitions 3) sys.allocation_units , to supply extra useful information
SELECT DB_NAME(tl.resource_database_id) as 'DB Name', tl.resource_type, tl.resource_associated_entity_id, tl.request_mode, tl.request_session_id, wt.blocking_session_id, obj.name as 'Object Name', obj.type_desc as 'Object Desc', pa.partition_id as 'Partition ID', tl.request_status, pa.rows as 'Partition rows numbers', al.type_desc as 'Index Desc', al.container_id as 'Storage container_id' FROM sys.dm_tran_locks as tl INNER JOIN sys.dm_os_waiting_tasks as wt ON tl.lock_owner_address = wt.resource_address LEFT JOIN sys.objects obj on obj.object_id = tl.resource_associated_entity_id LEFT JOIN sys.partitions pa on pa.hobt_id = tl.resource_associated_entity_id LEFT JOIN sys.allocation_units al on al.allocation_unit_id = tl.resource_associated_entity_id