How to Find sql server Locks

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

 Read More

SQL Server – sys.dm_os_waiting_tasks and performance

SQL Server - sys.allocation_units - SQL Server DBA

SQL Server – Troubleshoot open cursors with sys.dm_exec_cursors