In my previous post I talked about having your own database(s) and what information you could be collecting. One piece of information that you should be collecting is what deadlocks are occurring. There are a few of ways you can collect information on deadlocks, enabling trace flag 1222 and querying the error log, running profiler (urgh!), or querying the system_health event session (in SQL Server 2008 onwards).
In this post, I’d like to talk through setting up Event Notifications and Service Broker in order to be alerted when a deadlock occurs. I like this way of alerting for deadlocks as I think that you can get the most amount of information with minimal (if any) performance impact. I also administer instances of 2005 to 2012 and like to have one solution that fits all.
DISCLAIMER!! Please don’t do this for the first time in a live environment. Create a test database on a dev server so that you can play around (see the demo at the end).
Pre-requisite – DBMail setup and configured.
Right, here we go! Create a test db and then enable Service Broker for your database and set the TRUSTWORTHY property to ON. The TRUSTWORTHY property needs to be set to ON so that the stored procedure we are going to create later can run sp_send_dbmail.
USE [master]; GO CREATE DATABASE [DeadlockDemo]; GO ALTER DATABASE [DeadlockDemo] SET ENABLE_BROKER; GO ALTER DATABASE [DeadlockDemo] SET TRUSTWORTHY ON; GO
Second thing to do is create a table to hold the information on the deadlocks
USE [DeadlockDemo]; GO CREATE TABLE [dbo].[DeadLockLog] ([DeadLockID] INT IDENTITY (1, 1) NOT NULL, [DeadLockDetected] DATETIME NULL, [DatabaseName] SYSNAME NULL, [DeadLockXML] XML NULL, PRIMARY KEY CLUSTERED ([DeadLockID] ASC)); GO
We then need to create a queue to hold messages and a service to receive messages from the Event Notification we will setup. We specify [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] as the message type and contract (basically the direction of messages, from Event Notification to Service).
CREATE QUEUE [DeadlockQueue]; GO CREATE SERVICE [DeadlockService] ON QUEUE [DeadlockQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]); GO
Then run the following statement to create the Event Notification:-
CREATE EVENT NOTIFICATION [CaptureDeadlocks] ON SERVER WITH FAN_IN FOR DEADLOCK_GRAPH TO SERVICE N'DeadlockService', N'current database'; GO
It’s pretty straight forward, server wide, for deadlocks, to the service we have setup within the database. OK, next bit. When a new message about a deadlock is received, we want to record information on that deadlock and then be emailed. The way to do this is to create a stored procedure (substitute your own DBMail profile and email address in):-
CREATE PROCEDURE [dbo].[DeadlockNotification]
AS
DECLARE @DeadlockInfo XML
DECLARE @EventTime DATETIME
DECLARE @MsgBody XML
DECLARE @MsgType SYSNAME
DECLARE @DBName SYSNAME
DECLARE @DBMailBody SYSNAME
DECLARE @DBMailSubject SYSNAME
DECLARE @DBMailRecipient SYSNAME
DECLARE @DBMailProfile SYSNAME
SET @DBMailProfile = 'YOUR DBMAIL PROFILE'
SET @DBMailRecipient = 'YOUR EMAIL ADDRESS'
--Retrieve message from queue
WAITFOR
(RECEIVE TOP(1)
@MsgBody = CAST([message_body] AS XML),
@MsgType = message_type_name
FROM [DeadlockQueue]), TIMEOUT 6000;
--Retrieve information from the XML
SELECT
@DeadlockInfo = @MsgBody.query('(/EVENT_INSTANCE/TextData/deadlock-list)'),
@DBName = DB_NAME(@MsgBody.value('(//*/process/@currentdb)[1]', 'varchar(50)')),
@EventTime = @MsgBody.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
--Insert record into table if message conforms to correct type
IF @MsgType = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
INSERT INTO [dbo].[DeadLockLog]
(DeadLockDetected, DatabaseName, DeadLockXML)
VALUES
(@EventTime, @DBName, @DeadlockInfo)
--Set values to be included in email
SET @DBMailSubject = 'A deadlock has occurred on ' + @@SERVERNAME
SET @DBMailBody = 'A deadlock occurred in database ' +
@DBName + ' at ' + CONVERT(VARCHAR(20),@EventTime)
--Send email if message conforms to correct type
IF @MsgType = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
EXEC [msdb].[dbo].sp_send_dbmail
@profile_name = @DBMailProfile,
@Recipients = @DBMailRecipient,
@subject = @DBMailSubject,
@body = @DBMailBody;
GO
The procedure retrieves the message from the queue, pulls (or “shreds”) information from the XML, inserts it into a table and sends an email. Cool eh?
Finally, execute the following statement so that the stored procedure will run when a new message arrives in the queue:-
ALTER QUEUE [dbo].[DeadlockQueue]
WITH ACTIVATION
( STATUS = ON,
PROCEDURE_NAME = [dbo].[DeadlockNotification],
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF
);
GO
TESTING!!!!!
You can then test the functionality (in the dev environment that you have been doing this in……right?) by creating two tables and running concurrent UPDATE and SELECT statement in open transactions. Here’s the setup:-
CREATE TABLE [TEST1] (ID1 INT); GO CREATE TABLE [TEST2] (ID2 INT); GO INSERT INTO [TEST1] (ID1) VALUES (100) GO 1000 INSERT INTO [TEST2] (ID2) VALUES (100) GO 1000
All setup? Now run the following:-
USE [DeadlockDemo]; GO BEGIN TRAN UPDATE [Test1] SET ID1 = 1 WHERE ID1 = 100
Open up another session, and run:-
USE [DeadlockDemo]; GO BEGIN TRAN UPDATE [Test2] SET ID2 = 1 WHERE ID2 = 100 SELECT * FROM [Test1];
Go back to you first session, and run:-
SELECT * FROM [Test2];
One of the process will be chosen as the deadlock victim and you will receive an email advising that a deadlock occurred in your database. You can then query your table to get the XML, save it as a .XDL file and view the deadlock graph.
REFERENCES
http://technet.microsoft.com/en-us/library/ms178080.aspx
http://technet.microsoft.com/en-us/library/ms166029(v=sql.90).aspx
https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
DBA From The Cold 