More Dynamic Management Views: sys.dm_tran_locks

I’m working on the chapter on blocking in the new book. Explaining blocking of course means explaining locks. Prior to 2005, to understand locks, you went to sp_lock. Not anymore. Now you can query sys.dm_tran_locks. It’s so much more sophisticated than the old system procedure.  Best of all, the information within it is simply a view into the internal locking infrastructure, so you’re not placing extra load or extra processing on the system to marshal this data. A simple query to get basic locking information would look like this:

  SELECT tl.request_session_id

            ,tl.resource_database_id

            ,tl.resource_associated_entity_id

            ,tl.resource_type

            ,tl.resource_description

            ,tl.request_mode

            ,tl.request_status

  FROM sys.dm_tran_locks tl

That just outputs roughly the same information as sp_lock. Lots more detail, not available in sp_lock, is available if you need it. Things like resource_lock_partition to identify which partition a lock is on or  request_reference_count which shows how often the same lock has been requested by a given resource. Then, armed with this data, you can go after other dmv’s. Take, for a GLARING example, sys.dm_os_waiting_tasks. Hmmm if we were to combine something that showed locks with something that showed tasks that were waiting, what might you arrive at? BLOCKING!

The BOL shows a neat little query for just such an occasion:

SELECT
        t1.resource_type,
        t1.resource_database_id,
        t1.resource_associated_entity_id,
        t1.request_mode,
        t1.request_session_id,
        t2.blocking_session_id
    FROM sys.dm_tran_locks as t1
    INNER JOIN sys.dm_os_waiting_tasks as t2
        ON t1.lock_owner_address = t2.resource_address;

Clearly there is more here to explore. You can even go on to combine these dmv’s with the one’s that show the procedure cache so you can capture the execution plan of queries that are blocking or being blocked. I know dmv’s have been featured in a lot of articles and presentations lately, but I still think lots of people are unaware of just how useful they are. You need to examine this resource further if you’re working in SQL Server 2005/2008. More to come on sys.dm_os_waiting_tasks.

UPDATE: Typo corrected in the first paragraph. Thanks Jack.
ANOTHER UPDATE: More Typo’s corrected in the first paragraph. Thanks Gail