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 SPID
Blocked Status
Blocked Command
Blocked HostName
Blocked LoginName
Blocking SPID
Blocking Status
Blocking Command
Blocking HostName
Blocking LoginName
Database ID
Server Name
Start Time
End 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!!!