http://www.sqlservercentral.com/blogs/glennberry/2010/04/27/a-dmv-a-day-_1320_-day-27/

Printed 2014/09/17 05:56PM

A DMV a Day – Day 27

By GlennBerry, 2010/04/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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.