Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Find sql server Locks

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

 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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer