Event Notification - Locks
[Please check attached file: Event Notification.Locks.doc for the correct formated document.]
Problem description
Even within a database with a good ER model, resource dispute can happen, and sometimes it triggers deadlocks.
The probability of having lock problems in a database environment increases as the number of the concurrent accesses increases.
If we are talking about OLTP environments, locks should be controlled, analyzed carefully, and measures have to be taken to avoid them.
Even if the environment is not so critical, locks should still be managed. Measures should be taken to avoid raising performance problems.
My decision to manage lock problems has driven my attention to event notification. As I am going to show you on the next pages, it looked like as simple and efficient method to handle lock events.
By saying “handle lock events”, I meant to say: - Catch information about each occurrence of that event and used to not only send an alert email to the operator, but also to register it on a historic table for future analysis.
Implementation Overview
First we must have a database where all the structure is going to exist. Lets call it: DBA38
The database DBA38 must have Broker enable !
A queue and a related service have to be created to support this “event notification” management.
The events that we want to monitor, (and trigger actions when they occur) have to be chosen !
I´ve chosen the following ones:
- BLOCKED_PROCESS_REPORT;
- DEADLOCK_GRAPH;
- LOCK_ESCALATION;
- LOCK_DEADLOCK;
- LOCK_DEADLOCK_CHAIN.
After having seen the information that each event generated, I decided that in the two first cases, an email with a brief summary describing the event should be sent to our operator email (alerts.example.sqlport@gmail.com) to take immediate actions.
Requirement: In order to be possible to send any email out of our server, we have to use Database Mail, which requires some configuration. I am not focus this article over this configuration since there are a enough articles over this issue. Just search on your preferencial search engine for: “SQL Server 2008 database mail”, and walk through all required steps. (Just for a quick look I have added on the bottom of this document one picture of “Database Mail Configuration Wizard with some settings of the account that I have used on this demo).
Attention:
When configuring Database Mail, take note of the profile name used (profile name suggestion: Alerts), and just be sure that the profile used with msdb.dbo.sp_send_dbmail is the one you have defined before for this purpose !!! [check procedures code: EXECUTAR_ALERTA_DG; EXECUTAR_ALERTA_BPR]
Because is always good to maintain a historic of the lock events, the information returned by any event, should be registered in one table (EventNotification) for later analysis.
And because problems do occur, an error management routine has to exist, just to make sure everything is running like we want.
Implementation Overview Graphic
[Please check attached file: Event Notification.Locks.doc for the correct formated document where you can see the referenced image.]
Installation of code required to support solution
=============================================================================
In this step we will create all the structure required to proceed to our goal.
ATTENTION:
This code should be tested on a TEST system to make sure that everything works as planed !!!
You should check SQL Server Error LOG after each step execution, and even during Deadlock test, just to prevent any bigger issue (consequence of any wrong configuration !!! The rate of error message registered on SQL Server Error Log could be very intense and overload you SQL Server !!!)
1) Set blocked process threshold Option
[01.Set the blocked process threshold to 10 seconds.sql]
-- Creating a way to collect info about tables (Nr Records, Data Size)
2) Perform all required to:
- Create database DBA38;
- Enable Broker;
- Create Queue;
- Create Service;
- Add each notification event;
- Create table EventNotification (historic);
- Create table DBA_Error_Ocurred (error management!)
[02_06.Creat_All.sql]
-- Procedure that creates summary of each Blocked_Process_Report event
3) Create Procedure EXECUTAR_ALERTA_BPR
[07.Proc.Executar_alerta_BPR.sql]
-- Procedure that creates summary of each Deadlock_Graph_Report event
04) Create Procedure EXECUTAR_ALERTA_DG
[08.Proc.Executar_alerta_DG.sql]
-- Function used on the error management
05) Create function: CurrentDateTime
[09.Function.CurrentDateTime.sql]
-- Procedure executed when information related to the selected events is stored on the queue
06) Create Procedure usp_ProcessNotification
[10_usp_ProcessNotification.sql]
-- Activate the execution of the procedure usp_ProcessNotification when information related to the selected events is stored on the queue
07) Activation of the notification the queue
[11.Queue.Activation.ON.Procedure.sql]
Testing
For this purpose I have included a sample file with all required steps. Please check:
DeadlockTEST.HowTo.txt
Samples
Sample of the possible contents of: EventNotification table
[Please check attached file: Event Notification.Locks.doc for the correct formated document where you can see the referenced image.]
Sample of screen with Emails received
[Please check attached file: Event Notification.Locks.doc for the correct formated document where you can see the referenced image.]
Sample of email: Monitoring: Blocked/Blocking Process; Seq Number: 50
================================================================================
Monitoring Blocked Processes
================================================================================
Blocked Process Info
--------------------------------------------------------------------------------
Blocked Spid 54
status suspended
waittime 267308
last_batch 2010-08-04T17:02:40.390
program_name Microsoft SQL Server Management Studio - Query
hostname PRIVATE
hostprocess 13476
loginame PRIVATE\Rui
Blocked Statement
--------------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
exec spProc1
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Blocking Process Info
--------------------------------------------------------------------------------
Blocking Spid 53
status sleeping
last_batch 2010-08-04T17:02:39.700
program_name Microsoft SQL Server Management Studio - Query
hostname PRIVATE
hostprocess 13476
loginame PRIVATE\Rui
Blocking Statement
--------------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
BEGIN TRAN
UPDATE DeadlockTest
SET id = 99
WHERE id = 2
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
================================================================================
Detailed View
--------------------------------------------------------------------------------
<EVENT_INSTANCE>
<EventType>
BLOCKED_PROCESS_REPORT</EventType>
<PostTime>
2010-08-04T17:07:07.700</PostTime>
<SPID>
7</SPID>
<TextData>
<blocked-process-report>
<blocked-process>
<process id="processbff720" taskpriority="0" logused="0" waitresource="RID: 17:1:110:1" waittime="267308" ownerId="5818" transactionname="user_transaction" lasttranstarted="2010-08-04T17:02:40.390" XDES="0x12750280" lockMode="U" schedulerid="2" kpid="10384" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-08-04T17:02:40.390" lastbatchcompleted="2010-08-04T16:38:31.263" clientapp="Microsoft SQL Server Management Studio - Query" hostname="PRIVATE" hostpid="13476" loginname="PRIVATE\Rui" isolationlevel="read committed (2)" xactid="5818" currentdb="17" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="7" stmtstart="108" stmtend="356" sqlhandle="0x03001100210f0207aff61101c89d00000100000000000000"/>
<frame line="2" stmtstart="6" sqlhandle="0x01001100b736340328bd0112000000000000000000000000"/>
</executionStack>
<inputbuf>
exec spProc1
</inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2010-08-04T17:02:39.700" lastbatchcompleted="2010-08-04T17:02:39.700" clientapp="Microsoft SQL Server Management Studio - Query" hostname="PRIVATE" hostpid="13476" loginname="PRIVATE\Rui" isolationlevel="read committed (2)" xactid="5815" currentdb="17" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack/>
<inputbuf>
BEGIN TRAN
UPDATE DeadlockTest
SET id = 99
WHERE id = 2 </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
</TextData>
<DatabaseID>
17</DatabaseID>
<TransactionID>
5818</TransactionID>
<Duration>
267308000</Duration>
<StartTime>
2010-08-04T17:02:40.393</StartTime>
<EndTime>
2010-08-04T17:07:07.700</EndTime>
<ObjectID>
0</ObjectID>
<IndexID>
0</IndexID>
<ServerName>
PRIVATE\SQL2008ENT</ServerName>
<Mode>
4</Mode>
<LoginSid>
AQ==</LoginSid>
<EventSequence>
232</EventSequence>
<IsSystem>
1</IsSystem>
<SessionLoginName/>
</EVENT_INSTANCE>
--------------------------------------------------------------------------------
END
--------------------------------------------------------------------------------
Sample of email: Monitoring: Deadlock; Seq Number: 0
================================================================================
Monitoring Deadlock
================================================================================
--------------------------------------------------------------------------------
PostTime: Aug 4 2010 5:07PM
--------------------------------------------------------------------------------
deadlock victim: processbff8e8
<process-list>
<process id=processbff8e8
------------------------------------------
SPID: 55
waittime: 276412
clientapp: Microsoft SQL Server Management Studio - Query
hostname: PRIVATE
loginname: PRIVATE\Rui
currentdb: 17
================================================================================
inputbuf
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
exec spProc2
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<process id=processbff720
------------------------------------------
SPID: 54
waittime: 2644
clientapp: Microsoft SQL Server Management Studio - Query
hostname: PRIVATE
loginname: PRIVATE\Rui
currentdb: 17
================================================================================
inputbuf
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
exec spProc1
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--------------------------------------------------------------------------------
END
--------------------------------------------------------------------------------
Sample of the possible contents of table DBA_Error_Ocurred
[Please check attached file: Event Notification.Locks.doc for the correct formated document where you can see the referenced image.]
Each event in this table, (i.e.: ID:109) happened X times (161439) in that particular minute, but was only registered once (in that particular minute) on SQL Server Log. This implementation avoids error overflow on SQL Server Error log files, as you can imagine !
Sample of the Database Mail Configuration Wizard over the account used on this demo, to send emails
[Please check attached file: Event Notification.Locks.doc for the correct formated document where you can see the referenced image.]
The details that you have to configure here are related to the sender of the Event Notification message !!!
Requirement: In order to be possible to send any email out of our server, we have to use Database Mail, which requires some configuration. I am not focus this article over this configuration since there are a enough articles over this issue. Just search on your preferencial search engine for: “SQL Server 2008 database mail”, and walk through all required steps. (Just for a quick look I have added on the bottom of this document one picture of “Database Mail Configuration Wizard with some settings of the account that I have used on this demo).
Attention:
When configuring Database Mail, take note of the profile name used (profile name suggestion: Alerts), and just be sure that the profile used with msdb.dbo.sp_send_dbmail is the one you have defined before for this purpose !!! [check procedures code: EXECUTAR_ALERTA_DG; EXECUTAR_ALERTA_BPR]
Thanks for you time!
Regards,
Rui Ribeiro
-------------------------------------------------------------------------------------------------------------------------------
This article was produced/tested/validated over the following SQL Server version:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
- Clique Iniciar Sessão ou registar-se para colocar comentários