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;

Permalink 3 Comments

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

Permalink 6 Comments

Finally, the GDR is Released

December 1, 2008 at 2:17 pm (Visual Studio) (, , , )

From the Data Dude himself.

This is great news. I’ve been working with the CTP’s for several months now, telling the other DBA’s on my team that they had to wait until it was completely ready for release. It’s been a long wait, but I’m sure it’s worth it.

My congratulations to the team. I met several of you at the PASS Summit. I really apreciate the work you’ve put into this great tool. It really makes a difference in how we develop and deploy databases. The changes in the GDR are making a great utility even better.  Thanks Mr. Drapers. Thanks also to Jamie Laflen, especially for helping validate some of the ideas I presented at PASS. Thanks to all the rest of the team, whose names I don’t recall, especially the guy who took so long to explain to me what the “literal” was for in the reference page. Excellent work everyone. You guys should be proud.

Permalink Leave a Comment

« Previous page