Capturer les deadlocks avec une notification d’évènement

Ce script permet de mettre en place une journalisation des deadlocks dans une table de la base msdb, via une notification d’évènements.

Les informations du deadlock graph sont journalisées en XML dans la table msdb..DeadlockEventsXML. La vue msdb..DeadlockGraphs permet de visualiser les informations au format tabulaire. En particulier, le contenu de la colonne « XDL » de cette vue, sauvegardée au format de fichier .XDL permettra de visualiser le rapport graphique du deadlock dans SSMS.

N’oubliez pas de purger la table de temps en temps…

/*-------------------------------------------------------------------
[SCRIPT] Deadlocks Event Notification Installation Script  
[DATABASE] msdb
[DESCRIPTION] Installe suivi des deadlocks
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 20190202 
-------------------------------------------------------------------*/
USE msdb
GO
-- Configure les objets du service broker
IF EXISTS ( SELECT  *
            FROM    sys.services
            WHERE   name = 'NotifyDeadlockService' ) 
    DROP SERVICE NotifyDeadlockService 
GO
IF OBJECT_ID('[dbo].[NotifyDeadlockQueue]') IS NOT NULL
    AND EXISTS ( SELECT *
                 FROM   sys.service_queues
                 WHERE  name = 'NotifyDeadlockQueue' ) 
    DROP QUEUE [dbo].[NotifyDeadlockQueue]
GO
CREATE QUEUE NotifyDeadlockQueue
GO
CREATE SERVICE NotifyDeadlockService ON QUEUE NotifyDeadlockQueue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

-- Crée la table de journalisation
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DeadlockEventsXML](
	[EventNumber] [int] IDENTITY(1,1) NOT NULL,
	[EventType] [nvarchar](256) NULL,
	[EventTime] [datetime] NULL,
	[EventData] [xml] NULL,
PRIMARY KEY CLUSTERED 
(
	[EventNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
IF OBJECT_ID(N'[dbo].[LogDeadlockEvents]') IS NOT NULL
    AND OBJECTPROPERTY(OBJECT_ID(N'[dbo].[LogDeadlockEvents]'), N'IsProcedure') = 1 
    DROP PROCEDURE [dbo].[LogDeadlockEvents]
GO
-- procédure stockée de journalisation
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LogDeadlockEvents]
AS 
    BEGIN
	   SET NOCOUNT ON

	   DECLARE @message_body XML
		, @message_type_name NVARCHAR(256)
		, @dialog UNIQUEIDENTIFIER;

	   WHILE ( 1 = 1 ) 
		  BEGIN
			 BEGIN TRANSACTION

			 WAITFOR ( RECEIVE TOP ( 1 ) -- just handle one message at a time
				@message_type_name = message_type_name,  --the type of message received
				@message_body = message_body,      -- the message contents
				@dialog = conversation_handle -- the identifier of the dialog this message was received on
                    FROM NotifyDeadlockQueue ), TIMEOUT 2000; -- if the queue is empty for two seconds, give up and go away

			 IF ( @@ROWCOUNT = 0 ) 
				BEGIN
				    ROLLBACK TRANSACTION;
				    BREAK;
				END;

			 IF ( @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' ) 
				BEGIN
				    PRINT 'Fin du dialogue # '
					   + CAST(@dialog AS NVARCHAR(40));
				    END CONVERSATION @dialog;
				END;
			 ELSE 
				BEGIN
				    INSERT  INTO [dbo].[DeadlockEventsXML]
						  ( EventType
						  , EventTime
						  , EventData
						  )
				    VALUES  ( CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256))
						  , CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS DATETIME)
						  , @message_body
						  )
				END
			 COMMIT TRANSACTION
		  END 
    END
GO

-- Active la file d'attente, ce qui lance la réception des événements
ALTER QUEUE [dbo].[NotifyDeadlockQueue] WITH ACTIVATION ( STATUS = ON,
    PROCEDURE_NAME = [dbo].[LogDeadlockEvents], -- Lance un maximum de 2 instance de la procédure 
    MAX_QUEUE_READERS = 2, -- En tant que l'utilisateur qui a créé la file d'attente                     
    EXECUTE AS SELF ) 
-- Crée la notification
IF EXISTS ( SELECT  *
            FROM    sys.server_event_notifications
            WHERE   name = 'NotifyDeadlocks' ) 
    DROP EVENT NOTIFICATION NotifyDeadlocks ON SERVER 
GO
CREATE EVENT NOTIFICATION NotifyDeadlocks ON SERVER
        WITH fan_in FOR DEADLOCK_GRAPH TO SERVICE 'NotifyDeadlockService', 'current database' ;
GO
-- Vue de visualisation
CREATE VIEW DeadlockGraphs AS
SELECT [EventNumber]
      ,[EventType]
      ,[EventTime]
      ,[EventData]
	  ,EventData.query('/EVENT_INSTANCE/TextData/deadlock-list') AS XDL
  FROM [DeadlockEventsXML]

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

/*-------------------------------------------------------------------

[SCRIPT] Deadlocks Event Notification Installation Script

[DATABASE] msdb

[DESCRIPTION] Installe suivi des deadlocks

[MAJ PAR] DATAFLY - Arian Papillon

[DATEMAJ] 20190202

-------------------------------------------------------------------*/

USE msdb

-- Configure les objets du service broker

IF EXISTS ( SELECT *

FROM sys.services

WHERE name = 'NotifyDeadlockService' )

DROP SERVICE NotifyDeadlockService

IF OBJECT_ID('[dbo].[NotifyDeadlockQueue]') IS NOT NULL

AND EXISTS ( SELECT *

FROM sys.service_queues

WHERE name = 'NotifyDeadlockQueue' )

DROP QUEUE [dbo].[NotifyDeadlockQueue]

CREATE QUEUE NotifyDeadlockQueue

CREATE SERVICE NotifyDeadlockService ON QUEUE NotifyDeadlockQueue

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

-- Crée la table de journalisation

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[DeadlockEventsXML](

[EventNumber] [int] IDENTITY(1,1) NOT NULL,

[EventType] [nvarchar](256) NULL,

[EventTime] [datetime] NULL,

[EventData] [xml] NULL,

PRIMARY KEY CLUSTERED

(

[EventNumber] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

IF OBJECT_ID(N'[dbo].[LogDeadlockEvents]') IS NOT NULL

AND OBJECTPROPERTY(OBJECT_ID(N'[dbo].[LogDeadlockEvents]'), N'IsProcedure') = 1

DROP PROCEDURE [dbo].[LogDeadlockEvents]

-- procédure stockée de journalisation

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[LogDeadlockEvents]

BEGIN

SET NOCOUNT ON

DECLARE @message_body XML

, @message_type_name NVARCHAR(256)

, @dialog UNIQUEIDENTIFIER;

WHILE ( 1 = 1 )

BEGIN

BEGIN TRANSACTION

WAITFOR ( RECEIVE TOP ( 1 ) -- just handle one message at a time

@message_type_name = message_type_name, --the type of message received

@message_body = message_body, -- the message contents

@dialog = conversation_handle -- the identifier of the dialog this message was received on

FROM NotifyDeadlockQueue ), TIMEOUT 2000; -- if the queue is empty for two seconds, give up and go away

IF ( @@ROWCOUNT = 0 )

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END;

IF ( @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' )

BEGIN

PRINT 'Fin du dialogue # '

+ CAST(@dialog AS NVARCHAR(40));

END CONVERSATION @dialog;

END;

ELSE

BEGIN

INSERT INTO [dbo].[DeadlockEventsXML]

( EventType

, EventTime

, EventData

)

VALUES ( CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256))

, CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS DATETIME)

, @message_body

)

END

COMMIT TRANSACTION

END

-- Active la file d'attente, ce qui lance la réception des événements

ALTER QUEUE [dbo].[NotifyDeadlockQueue] WITH ACTIVATION ( STATUS = ON,

PROCEDURE_NAME = [dbo].[LogDeadlockEvents], -- Lance un maximum de 2 instance de la procédure

MAX_QUEUE_READERS = 2, -- En tant que l'utilisateur qui a créé la file d'attente

EXECUTE AS SELF )

-- Crée la notification

IF EXISTS ( SELECT *

FROM sys.server_event_notifications

WHERE name = 'NotifyDeadlocks' )

DROP EVENT NOTIFICATION NotifyDeadlocks ON SERVER

CREATE EVENT NOTIFICATION NotifyDeadlocks ON SERVER

WITH fan_in FOR DEADLOCK_GRAPH TO SERVICE 'NotifyDeadlockService', 'current database' ;

-- Vue de visualisation

CREATE VIEW DeadlockGraphs AS

SELECT [EventNumber]

,[EventType]

,[EventTime]

,[EventData]

,EventData.query('/EVENT_INSTANCE/TextData/deadlock-list') AS XDL

FROM [DeadlockEventsXML]