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/

Somewhere to relax, get away from it all (by all I mean pesky Developers wanting code deployed on a bleeding Friday afternoon) and generally unwind. Your worries seem to drift off until there’s a bang on the door…there’s been an issue in Production and you need to investigate. You heave yourself up, over to your desk and connect to your other personal space.

Every DBA should have their own database setup within the environment(s) that they monitor. Depending on the environment there could be one per instance monitored or one central database. Anyway, the point is to have an area specifically for yourself (and any other DBAs) so that you can store scripts for analysis and data on the instance(s) of SQL being monitored.

But what information should be captured? At a (bare) minimum I think the following information on a server should be captured:-

Backups – you want to know about your backups…….right?
Database Size – how big are you databases, how much have they grown over a set period of time?
Disk Space – how much free disk space do you have on your drives hosting SQL databases?
Index Stats – how are the indexes in the databases being used? Are they efficient?
Wait Statistics – what are the top waits on your server?

OK, so there are many third party tools out there that will collect this information (and more) for you. However, I still think a DBA should be able to collect this information by using scripts in their own script archive. By setting up these monitors yourself, you learn about the DMVs within SQL Server and how they can be used to troubleshoot a problem. You don’t want to be using them for the first time after an issue has occurred!

But what else should you be looking at? I’ve recently revamped my monitoring database to include the following:-

Auto-Growth Events – which databases are growing?
Blocking – who is being blocked? Are there any patterns?
Deadlocking – what deadlocks have occurred, what process was the victim?
Log Space – how much log space is being consumed for each database?
Suspect Pages – which pages have generated 823/824 errors?
SQL Error Log Auditing – what errors are being recorded?

Once this data is being collected, you can then decide what alerting you want to place on top. Certain events you’ll want to know about immediately (think deadlocks) but do you want to know about all auto-growth events? Maybe you do…by having your own personal space you can decide what you want to see, and tailor your space to what you need.