SQLServer 2005: Capturing Processes Waiting / Blocked

OK - it's out of order (this is supposed to be a followup on partitioning ), but I'm always writing some code and then later losing it and re-creating it, so I decided to put this up today...

Everyonce in a while we've had short 'blocking / waiting' where things back up in the system. It's kindof a pain in the you know what when it disappears after a few minutes, so I wrote some code to grab all the SQL that's waiting, and stick it in a temp table for later use. Obviously, a working table in a separate database might be more practical, and maybe a few more options on the stored procedures, but for now, here goes:

1. Create a procedure to check for waiting SQL:

CREATE PROCEDURE blocked_queries]
@thresh DECIMAL (5,2) = 1.0,
@dbname VARCHAR (128) = NULL
AS
DECLARE
@dbstart INT,
@dbend INT
BEGIN
IF @dbname IS NOT NULL
BEGIN
SET @dbstart = DB_ID (@dbname)
SET @dbend = @dbstart + 1
END
ELSE
BEGIN
SET @dbstart = 4
SET @dbend = 999
END

SELECT DB_NAME (b.database_id) AS [database],
x.host_name AS blocked_host,
a.session_id AS sid,
a.wait_type,
ROUND (CAST (a.wait_duration_ms / 1000.0 AS DECIMAL (5,2)),2,1) as
wait_time_sec,
(
SELECT
SUBSTRING
(text, b.statement_start_offset/2 + 1,
(CASE
WHEN b.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE b.statement_end_offset
END - b.statement_start_offset
) / 2
)
FROM sys.dm_exec_sql_text(b.sql_handle)
) AS blocked_query ,
a.blocking_session_id AS blocking_sid,
(
SELECT
SUBSTRING
(text, c.statement_start_offset/2 + 1,
(CASE
WHEN c.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE c.statement_end_offset
END - c.statement_start_offset
) / 2
)
FROM sys.dm_exec_sql_text(c.sql_handle)
) AS blocking_query
FROM sys.dm_os_waiting_tasks a
join sys.dm_exec_sessions x ON a.session_id = x.session_id
join sys.dm_exec_requests b ON a.session_id = b.session_id
left outer join sys.dm_exec_requests c ON a.blocking_session_id =
c.session_id
WHERE b.database_id >= @dbstart
AND b.database_id < @dbend
AND a.wait_duration_ms / 1000.00 > @thresh
END

2. Now Create the Temporary Table:

create table #blocked_queries
(
database_name varchar(20),
blocked_host varchar(50),
sid int,
wait_type varchar(20),
wait_time_sec decimal (5,2),
blocked_query varchar(4000),
blocking_sid int,
blocking_query varchar(4000),
lock_time datetime default getdate()
)

3. Last Use the Stored Procedure to Popoulate the Temporary Table:

declare
@blocked_count int,
@done int
begin
select @blocked_count = count(*) from #blocked_queries
set @done = 10000

while @blocked_count <= @done begin insert into #blocked_queries ( database_name, blocked_host, sid , wait_type, wait_time_sec, blocked_query, blocking_sid, blocking_query ) exec tp_blocked_queries 1.0,

select @blocked_count = count(*) from #blocked_queries
end
end

Now all you have to do is hope you catch the culprit in action, and that it won't be too difficult to fix, right? :)