Dynamic Management Views Put to Work on Blocking

December 3, 2008 at 6:53 pm (SQL Server 2005, SQL Server 2008, TSQL) (, , , , , )


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 Comments

  1. DavidB said,

    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

  2. scarydba said,

    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.

  3. DavidB said,

    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!

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: