I forgot all about this, but a script I wrote on using all the new functionality of dynamic management views & functions to do index defragmentation and rebuilds got published over at SQL Server Central.
It could stand a bit of tweaking, but gets the job done on several of the systems I’ve tested it on so far.
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
,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
,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;
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:
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