Deadlock Detection and Reporting part-I
This will be a 2 part series about deadlock. First part will be about how you detect deadlock in SQL Server and collect that information. Second part will be about reporting from the collected information.
Deadlock is a transient event till one of the process becomes a victim. Unless you persist all the details about the deadlock and get notified it is not easy to solve a problem. This process will help you detecting deadlock as it happen, get details about the deadlock and also find the frequency. Eventually you will be able to identify if there is a trend/pattern of deadlock in your environment.
You can use built in agent alert leveraging WMI query.
Create a built in alert.
USE [MSDB] GO EXEC msdb.dbo.sp_add_alert @name = N'WMI - Query DEADLOCK_GRAPH', @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 1, @notification_message = N'Deadlock detected in', @wmi_namespace = N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER', --for named instance @wmi_namespace = N'\.rootMicrosoftSqlServerServerEventsNamedInstance', @wmi_query = N'SELECT * FROM DEADLOCK_GRAPH' GO
Create a table in your DBA database.
USE [DBA] GO CREATE TABLE [dbo].[DeadlockEvents] ( [Id] [int] IDENTITY(1, 1) NOT NULL , [AlertTime] [datetime2](7) NOT NULL , [DeadlockGraph] xml NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[DeadlockEvents] ADD CONSTRAINT [DF_AlertTime] DEFAULT (SYSDATETIME()) FOR [AlertTime] GO
In the response tab of you alert you call a SQL agent job. That job will execute following code.
INSERT INTO DBA..DeadlockEvents ( DeadlockGraph ) VALUES ( N'$(ESCAPE_SQUOTE(WMI(TextData)))' )
I will have a 2nd step for this job and more on deadlock reporting in the2nd partof the blog.