Using Data Management Views - Blocking

Finally, here's something useful:

Data management views and functions in SQL 2005 give you an easy way to look at critical server information but there is not a lot of documentation about using them. Here is an example of using a couple to record blocking information, including the blocker code and the blocked code. I've written it to run in an endless loop taking snapshots of blocking on the server at 5 second intervals. The interval is configurable of course. If you have ever tried digging this information out of a server by using sp_blocker_pss80, you will love the simplicity of this method.

In this example I have included code to create a temp table to store results but you might want to create a permanent table somewhere.

The loop will run from a query window until you kill it, then it will retrieve results and display them. This has the disadvantage of not letting you see results until you are finished. If you want to view blocking interactively you can just execute the SELECT part of the INSERT statement in another window.

Heres the code to create the table:

/*
create table #BlockingCode
(resource_type nvarchar(120)
,[database] nvarchar(128)
,[blk object] bigint
,request_mode nvarchar(120)
,request_session_id int
,waiter_text text
,blocking_session_id int
,blocker_text text
,batchdate datetime default getdate() )
*/


-- here's the code to capture and insert blocking information.

WHILE 1=1 -- endless loop, be careful
BEGIN
insert into #BlockingCode
(resource_type
,[database]
,[blk object]
,request_mode
,request_session_id
,waiter_text
,blocking_session_id
,blocker_text
)
select t1.resource_type
,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode
,t1.request_session_id -- spid of waiter
,(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id) as waiter_text
,t2.blocking_session_id -- spid of blocker
,(select text from sys.sysprocesses as p --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_text
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address
WAITFOR DELAY '00:00:05'
END
GO

--retrieve results
select * from #BlockingCode

This code has the disadvantage of not showing the results until you stop loop execution, then it displays the results collected. If you want to take a quick look at what kind of blocking is going on, just copy the select clause of the insert statement into another query window and execute it when you wish.