DaveWentzel.com            All Things Data

Blocked Process Threshold

 

Blocked Process Threshold
SQL2K5 has a new option to proactively monitor blocking and deadlocking instances. 
 
sp_configure  with value of 10 says if there is blocking for over 10 seconds the server will raise an event that can be captured by using the event notification mechanism to perform an action, and it is also seen in Profiler. 
 
Example:  logging to an audit table. 
 
sp_configure 'show advanced options', 1;
reconfigure with override;
sp_configure 'blocked process threshold',10;
reconfigure with override;
 
create table master.dbo.tblBlocking
(id INT identity(1,1) not null primary key,
server_name varchar(100) null,
blocker_spids varchar(8000) null,
event_info xml null,
date_time datetime not null default getdate());
go
 
 
The server reaises the BLOCKED_PROCESS_REPORT event when there is a blocking for 10 seconds and every 10 seconds after that until blocking is resolved.  The event is captured by the event notification mechanism, which places a message in the Service Broker q, which in turn activates the stored procedure, which finally makes an entry into the audit table. 
 
CREATE PROC dbo.sp_notifyBlocking
AS
BEGIN
declare @probclients TABLE(spid SMALLINT, ecid SMALLINT, blocked SMALLINT, waittype BINARY(2), dbid SMALLINT, PRIMARY KEY (blocked, spid, ecid));
 
INSERT @probclients
SELECT spid, ecid, blocked, waittype, dbid
FROM master.dbo.sysprocesses
WHERE blocked !=0 OR waittype != 0x0000;
 
INSERT @probclients
SELECT DISTINCT blocked, 0,0,0x0000,0
FROM @probclients
WHERE blocked !=0 AND blocked NOT IN (
select spid FROM @probclients);
 
declare @headblockerspids varchar(4000)
select @headblockerspids =
coalesce (@headblcokerspids + ', ', '') + CAST(spid as varchar(5))
from @probclients
where blocked = 0 and
spid in (select blocked from @probclients where spid != 0);
 
--receive q message
declare @qmessage xml
declare @SBMessage table (msgtextxml);
receive case(message_body as xml) as msgtext
from BlockingMonitoringQueue INTO @SBMessage;
select top1 @qmessage = msgtext from @sbmessage;
 
--insert into audit table
insert into master.dbo.tblblocking (server_name, blocker_spids, event_info)
values (@@servername, @headblockerspids,@qmessage);
end;
go
 
 
The last step is to enable SB in the user database, create SB objects such as the service and the q, and set up the event notification to respond to the event. 
 
alter database name set ENABLE_BROKER;
 
CREATE QUEUE BlockingMonitoringQueue
WITH STATUS = ON,
ACTIVATION(
PROCEDURE_NAME=master.dbo.sp_notifyBlocking,
MAX_QUEUE_READERS=5,
EXECUTE AS SELF)
ON [DEFAULT];
GO
 
CREATE SERVICE [BlockingMonitoringService] ON QUEUE BlockingMonitoringQueue
go
 
CREATE EVENT NOTIFICATION EventNotifyBlocking
ON SERGVER
FROM BLOCKED_PROCESS_REPORT
TO SERVICE 'BlockingMonitoringService','current database';
GO
 
 
 

Add new comment