More Dynamic Management Views: sys.dm_tran_locks

December 2, 2008 at 9:05 am (SQL Server 2005, SQL Server 2008, TSQL) (, , , , , , , )


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

6 Comments

  1. Jack D Corbett said,

    Grant,

    Did you mean sp_lock in you r second sentence, “Prior to 2005, to understand blocks, you went to sp_blocks.”? I’ve never heard of sp_blocks and can’t seem to find it.

    Good post though and I definitely need to spend time learning about the DMV’s

  2. scarydba said,

    Yes. My bad. Thanks for pointing it out. I updated the content.

  3. Gail said,

    Likewise “Explaining blocking of course means explaining blocking.”

    Isn’t recursion in a different chapter?

  4. scarydba said,

    That’s it!

    My editor has been sacked. A new editor has been hired at 5 times the pay of the previous editor.

  5. Gail said,

    5×0?

    It seems you have blocks on the brain. I wonder why…. ;-)

  6. scarydba said,

    Wait til next week. It’ll be all deadlocking all the time. If you want to send a few 2008 hints or snippets now to get them out of the way and avoid the rush next week, please feel free.

    The week after that it’ll be cursors (bleh!). I’m not looking forward to that one. I’ll have to shower frequently.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: