Pages

Friday, June 22, 2007

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? :)



No comments: