Read more: http://www.blogsmonetize.com/2010/10/how-to-use-syntax-highlighter-3083-in.html#ixzz1DHzvEgBA

Friday, March 4, 2011

Finding out blocks/locks in your db(Mail Notification)

Blocking and locking is a main area for performance tuning and to ensure a good health of production system. Here is a script to notify DBAs about the locking occurs in the production system.It uses service brokers to notify the lock information.

As a first step, we need to set a threshold value for blocked processes. This can be set using sp_configure as below:

--Set a throshold for blocked process to 10 Sec
sp_configure 'blocked process threshold (s)',10


Once we set a threshold value for the blocking, the next step is to create a queue and service for the event to put an entry in the queue.

--Create a Queue for the event notification
CREATE QUEUE DBA_Prod_Lock_Queue
--Create a Service on queue for the event notification
CREATE SERVICE DBA_Prod_Lock_Service
ON QUEUE DBA_Prod_Lock_Queue ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )


We need to set up a event notification to notify the event of blocking for the threshold.

--Create an event notification for the blocked process report
CREATE EVENT NOTIFICATION DBA_Prod_Notify_Locks
ON SERVER
WITH fan_in
FOR blocked_process_report
TO SERVICE 'DBA_Prod_Lock_Service', 'current database';


Verify the messages are coming to the queue whenever a lock exceeds a threshold and the event notification.

SELECT cast( message_body as xml ), *
FROM DBA_Prod_Lock_Queue
SELECT * FROM sys.server_event_notifications


The below table is a physical table in our local db to hold the event notification further analysis.

Create Table DBA_ProductionMonitor_EventLockInformation
(
SeqID BigInt Identity(1,1),
MessageBody XML,
DatabaseID Int,
Process XML,
Is_Notified Bit Default(0)
)


The below procedure recieve the messages from the queue and push into a local table to proceed with our functionality.

Create Proc usp_DBA_ProductionMonitor_ServiceProc
AS
Begin
DECLARE @msgs TABLE ( message_body xml not null,
message_sequence_number int not null );
RECEIVE message_body, message_sequence_number
FROM DBA_Prod_Lock_Queue
INTO @msgs;
Insert Into DBA_ProductionMonitor_EventLockInformation(MessageBody,DatabaseID,Process)
SELECT message_body,
DatabaseId = cast( message_body as xml ).value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ),
Process = cast( message_body as xml ).query( '/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process' )
FROM @msgs
ORDER BY message_sequence_number
End


The below procedure is used to extract the relevant information from the local table where the messages been pushed from queue and send a mail for lock notification.

Alter Procedure usp_DBA_ProductionMonitor_LockInformation
As
Begin
declare @body varchar(max) =''
Create Table #Temp_SeqIds (SeqId Int)
Insert into #Temp_SeqIds Select SeqID From DBA_ProductionMonitor_EventLockInformation Where Is_Notified=0
IF Exists(Select 1 From #Temp_SeqIds)
Begin
set @body = cast( (
select td =
Cast(BlockedSPID as Varchar(50))
+ ''
+ Cast(BlockedStatus as Varchar(MAX))
+ ''
+ CAST( BlockedCommand as Varchar(MAX))
+ ''
+ Cast(BlockedHostName as Varchar(50))
+ ''
+ Cast(BlockedLoginName as Varchar(MAX))
+ ''
+ CAST( BlockingSPID as Varchar(MAX))
+ ''
+ Cast(BlockingStatus as Varchar(MAX))
+ ''
+ CAST( BlockingCommand as Varchar(MAX))
+ ''
+ Cast(BlockingHostName as Varchar(50))
+ ''
+ Cast(BlockingLoginName as Varchar(MAX))
+ ''
+ CAST( DataBaseID as Varchar(MAX))
+ ''
+ CAST( ServerName as Varchar(MAX))
+ ''
+ CAST( StartTime as Varchar(MAX))
+ ''
+ CAST( EndTime as Varchar(MAX))
from (
SELECT
--Blocked Information
BlockedSPID=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid)[1]', 'varchar(100)' ) ,
BlockedStatus=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@status)[1]', 'varchar(100)' ) ,
BlockedCommand=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process)[1]', 'varchar(max)' ) ,
BlockedHostName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@hostname)[1]', 'varchar(100)' ) ,
BlockedLoginName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@loginname)[1]', 'varchar(100)' ) ,
--Blocking Information
BlockingSPID = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid)[1]', 'varchar(100)' ) ,
BlockingStatus = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@status)[1]', 'varchar(100)' ) ,
BlockingCommand = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process)[1]', 'varchar(max)' ) ,
BlockingHostName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@hostname)[1]', 'varchar(100)' ) ,
BlockingLoginName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@loginname)[1]', 'varchar(100)' ) ,
--General Information
DataBaseID = MessageBody.value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ) ,
ServerName = MessageBody.value( '(/EVENT_INSTANCE/ServerName)[1]', 'varchar(100)') ,
StartTime = MessageBody.value( '(/EVENT_INSTANCE/StartTime)[1]', 'datetime' ) ,
EndTime = MessageBody.value( '(/EVENT_INSTANCE/EndTime)[1]', 'datetime' )
FROM DBA_ProductionMonitor_EventLockInformation A
Inner Join #Temp_SeqIds B On A.SeqID = B.SeqId
) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body = ' '
+ ' '
+ ' '
+ ' '
+ ' '
+ ''
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
Blocked SPIDBlocked StatusBlocked CommandBlocked HostNameBlocked LoginNameBlocking SPIDBlocking StatusBlocking CommandBlocking HostNameBlocking LoginNameDatabase IDServer NameStart TimeEnd Time
'
print @body
EXEC msdb.dbo.sp_send_dbmail @PROFILE_NAME = '',
@recipients='',
@subject = 'DBA Notification - Production Lock Occured.',
@body = @body,
@body_format = 'HTML' ;
Update A Set Is_Notified = 1 From DBA_ProductionMonitor_EventLockInformation A
Inner Join #Temp_SeqIds B On A.SeqID = B.SeqId
End


The below script to enable and disable the queue to maintain the functionality.

--Disable Queue
ALTER QUEUE [dbo].[DBA_Prod_Lock_Queue] WITH STATUS = OFF , RETENTION = OFF , ACTIVATION ( STATUS = OFF , MAX_QUEUE_READERS = 0 , EXECUTE AS OWNER )
--Enable Queue
ALTER QUEUE [dbo].[DBA_Prod_Lock_Queue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = OFF , MAX_QUEUE_READERS = 0 , EXECUTE AS OWNER )


Hope the script would be useful for all of us!!!

2 comments:

Anonymous said...

Hi

first of all thanks for your scripts. they are really helpful.

how the above stored proc will be triggered automatically whenever blocking occurred

dbo.usp_DBA_ProductionMonitor_ServiceProc

Thanks
JrDBA

Anonymous said...

In which SQL version it was tested is it working for SQL server 2000 database...i believe no as event notification was not supported in SQL 2000 and even in SQL 2005 was very limited.