Dynamic Management Views Put to Work on Blocking

This is my first pass at a modern (2005/2008) blocking monitoring script. I think it’s a decent blocking script to capture information about blocks as they are occurring. Filters can be applied and it wouldn’t be hard at all to add on other information such as execution plans, plan hash, etc.

SELECT  tl.request_session_id AS WaitingSessionID

       ,wt.blocking_session_id AS BlockingSessionID

       ,wt.resource_description

       ,wt.wait_type

       ,wt.wait_duration_ms

       ,DB_NAME(tl.resource_database_id) AS DatabaseName

       ,tl.resource_associated_entity_id AS WaitingAssociatedEntity

       ,tl.resource_type AS WaitingResourceType

       ,tl.request_type AS WaitingRequestType

       ,wrt.[text] AS WaitingTSql

       ,btl.request_type BlockingRequestType

       ,brt.[text] AS BlockingTsql

FROM    sys.dm_tran_locks tl

        JOIN sys.dm_os_waiting_tasks wt

        ON tl.lock_owner_address = wt.resource_address

        JOIN sys.dm_exec_requests wr

        ON wr.session_id = tl.request_session_id

        CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt

        LEFT JOIN sys.dm_exec_requests br

        ON br.session_id = wt.blocking_session_id

        OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) AS brt

        LEFT JOIN sys.dm_tran_locks AS btl

        ON br.session_id = btl.request_session_id;

3 thoughts on “Dynamic Management Views Put to Work on Blocking

  • DavidB

    I think it produces a bit of a cartisian product when blocking exists. Haven’t figured out where yet but definitely something funky going on. I had 2 blocking processes and it got to 984 rows prior to me killing it.

    Glad my blocking count was not that high. 🙂

    Sorry….

    David

  • scarydba

    Hmmm… It’s not a cartesian product because the join criteria is pretty clear… Um, I’ve only so far seen small scale blocking. Maybe it has an issue when large number of rows or pages are blocked.

    Did you try putting a filter on wait_duration_ms column to only go after long term blocks, say make it 5000 to start (5 seconds)? I’d just be curious.

  • DavidB

    Yes, you are correct in that it is not a cartesian join but the way that it handles the rows / pages makes it a bit rough on the return set. 🙂

    I’ll take a look at it some more when I have a few more minutes to breathe. Ack, a wee bit busy at the moment.

    I’ll let you know what I find.

    Thanks!

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.