SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A DMV a Day – Day 27

The DMV for Day 27 is sys.dm_tran_locks, which is described by BOL as:

Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

This DMV can be used to help identify locking and blocking issues.  This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Look at active Lock Manager resources for current database
SELECT request_session_id, DB_NAME(resource_database_id) AS [Database], 
resource_type, resource_subtype, request_type, request_mode, 
resource_description, request_mode, request_owner_type
FROM sys.dm_tran_locks
WHERE request_session_id > 50
AND resource_database_id = DB_ID()
AND request_session_id <> @@SPID
ORDER BY request_session_id;
-- Look for blocking
SELECT tl.resource_type, tl.resource_database_id,
       tl.resource_associated_entity_id, tl.request_mode,
       tl.request_session_id, wt.blocking_session_id, 
       wt.wait_type, wt.wait_duration_ms
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
ON tl.lock_owner_address = wt.resource_address
ORDER BY wait_duration_ms DESC;

I have a couple of sample queries using sys.dm_tran_locks shown above. The first query shows lock types and their status by SPID, filtered by the current database, and eliminating the current connection and eliminating system SPIDs.  The second query will give you some information about any blocking that may be occurring, instance-wide.  You typically need to run both of these queries multiple times to catch blocking, unless you have pretty severe blocking going on.


Posted by Jason Brimhall on 27 April 2010

Excellent Scripts

Posted by Dukagjin Maloku on 28 April 2010

Very nice info about tran locks, thanks!

Posted by Anonymous on 29 April 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, A DMV a Day ??? Day 27 - Glenn Berry's SQL Server Performance         [sqlservercentral.com]        on Topsy.com

Leave a Comment

Please register or log in to leave a comment.