Capturer les verrous bloquants avec une notification d’évènement

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

Il est basé sur une notification d’évènement sur le déclenchement du « blocked process report », configuré à 15 secondes : le message est transmis de manière asynchrone par le service broker et le rapport est stocké en XML dans la table msdb..BlockingEventsXML. La vue msdb..BlockingEvents permet de visualiser les informations au format tabulaire.

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

/*-------------------------------------------------------------------
[SCRIPT] Blocked Process Report & Event Notification Installation Script  
[DATABASE] msdb
[DESCRIPTION] Installe suivi des verrous supérieurs à 15 secondes
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 20190202
-------------------------------------------------------------------*/
-- Configurer le "blocked process threshold" à 15 secondes
USE MASTER
GO
EXEC sys.sp_configure N'show', 1
RECONFIGURE
GO
EXEC sys.sp_configure N'blocked process threshold', 15
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show', 0
RECONFIGURE
GO

-- Configurer le service broker et les objets dans msdb
use [msdb]
GO
IF EXISTS ( SELECT  *
            FROM    sys.services
            WHERE   name = 'NotifyBlockingService' ) 
    DROP SERVICE NotifyBlockingService 
GO
IF OBJECT_ID('[dbo].[NotifyBlockingQueue]') IS NOT NULL
    AND EXISTS ( SELECT *
                 FROM   sys.service_queues
                 WHERE  name = 'NotifyBlockingQueue' ) 
    DROP QUEUE [dbo].[NotifyBlockingQueue]
GO
CREATE QUEUE NotifyBlockingQueue
GO
CREATE SERVICE NotifyBlockingService ON QUEUE NotifyBlockingQueue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

-- Table de journalisation
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BlockingEventsXML](
	[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].[LogBlockingEvents]') IS NOT NULL
    AND OBJECTPROPERTY(OBJECT_ID(N'[dbo].[LogBlockingEvents]'), N'IsProcedure') = 1 
    DROP PROCEDURE [dbo].[LogBlockingEvents]
GO
-- Procédure stockée de journalisation
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LogBlockingEvents]
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 NotifyBlockingQueue ), 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].[BlockingEventsXML]
						  ( 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].[NotifyBlockingQueue] WITH ACTIVATION ( STATUS = ON,
    PROCEDURE_NAME = [dbo].[LogBlockingEvents], -- 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 = 'NotifyLocks' ) 
    DROP EVENT NOTIFICATION NotifyLocks ON SERVER 
GO
CREATE EVENT NOTIFICATION NotifyLocks ON SERVER
        WITH fan_in FOR blocked_process_report TO SERVICE 'NotifyBlockingService', 'current database' ;
GO


-- Vue pour examiner les évènements
CREATE VIEW dbo.BlockingEvents
AS
    SELECT  a.EventTime
		, EventData.value('(/EVENT_INSTANCE/StartTime)[1]', 'datetime') StartTime
		, DB_NAME(EventData.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int')) DBName
		, Y.x.value('@spid', 'nvarchar(45)') BlockingSpid
		, X.x.value('@spid', 'nvarchar(45)') BlockedSpid
		, X.x.value('@id', 'nvarchar(45)') BlockedId
		, Y.x.value('@hostname', 'nvarchar(45)') BlockingFromHost
		, Y.x.value('@loginname', 'nvarchar(45)') BlockingFromLogin
		, Y.x.value('@clientapp', 'nvarchar(100)') BlockingClientApp
		, Y.x.value('@isolationlevel', 'nvarchar(45)') BlockingFromIso
		, Y.x.value('data(.)', 'varchar(5000)') AS BlockingSQLcmd
		, Y.x.value('@waitresource', 'nvarchar(45)') BlockingObject 
		, DB_NAME(X.x.value('@currentdb', 'int')) Blockeddb
		, X.x.value('@waitresource', 'nvarchar(45)') BlockedObject
		, X.x.value('@lockMode', 'nvarchar(45)') BlockedBlockLockMode
		, X.x.value('@hostname', 'nvarchar(45)') BlockedFromHOst
		, X.x.value('@loginname', 'nvarchar(45)') BlockedFromLogin
		, X.x.value('@clientapp', 'nvarchar(100)') BlockedClientApp
		, X.x.value('@isolationlevel', 'nvarchar(45)') BlockedFromIso
		, X.x.value('data(.)', 'varchar(5000)') AS BlockedSQLcmd
    FROM	  dbo.BlockingEventsXML a
		  CROSS APPLY EventData.nodes('EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process')
		  AS X ( x )
		  CROSS APPLY EventData.nodes('EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process')
		  AS Y ( x ) 
--ORDER BY EventTime desc

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

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

[SCRIPT] Blocked Process Report & Event Notification Installation Script

[DATABASE] msdb

[DESCRIPTION] Installe suivi des verrous supérieurs à 15 secondes

[MAJ PAR] DATAFLY - Arian Papillon

[DATEMAJ] 20190202

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

-- Configurer le "blocked process threshold" à 15 secondes

USE MASTER

EXEC sys.sp_configure N'show', 1

RECONFIGURE

EXEC sys.sp_configure N'blocked process threshold', 15

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N'show', 0

RECONFIGURE

-- Configurer le service broker et les objets dans msdb

use [msdb]

IF EXISTS ( SELECT *

FROM sys.services

WHERE name = 'NotifyBlockingService' )

DROP SERVICE NotifyBlockingService

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

AND EXISTS ( SELECT *

FROM sys.service_queues

WHERE name = 'NotifyBlockingQueue' )

DROP QUEUE [dbo].[NotifyBlockingQueue]

CREATE QUEUE NotifyBlockingQueue

CREATE SERVICE NotifyBlockingService ON QUEUE NotifyBlockingQueue

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

-- Table de journalisation

USE [msdb]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[BlockingEventsXML](

[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].[LogBlockingEvents]') IS NOT NULL

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

DROP PROCEDURE [dbo].[LogBlockingEvents]

-- Procédure stockée de journalisation

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[LogBlockingEvents]

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 NotifyBlockingQueue ), 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].[BlockingEventsXML]

( 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].[NotifyBlockingQueue] WITH ACTIVATION ( STATUS = ON,

PROCEDURE_NAME = [dbo].[LogBlockingEvents], -- 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 = 'NotifyLocks' )

DROP EVENT NOTIFICATION NotifyLocks ON SERVER

CREATE EVENT NOTIFICATION NotifyLocks ON SERVER

WITH fan_in FOR blocked_process_report TO SERVICE 'NotifyBlockingService', 'current database' ;

-- Vue pour examiner les évènements

CREATE VIEW dbo.BlockingEvents

SELECT a.EventTime

, EventData.value('(/EVENT_INSTANCE/StartTime)[1]', 'datetime') StartTime

, DB_NAME(EventData.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int')) DBName

, Y.x.value('@spid', 'nvarchar(45)') BlockingSpid

, X.x.value('@spid', 'nvarchar(45)') BlockedSpid

, X.x.value('@id', 'nvarchar(45)') BlockedId

, Y.x.value('@hostname', 'nvarchar(45)') BlockingFromHost

, Y.x.value('@loginname', 'nvarchar(45)') BlockingFromLogin

, Y.x.value('@clientapp', 'nvarchar(100)') BlockingClientApp

, Y.x.value('@isolationlevel', 'nvarchar(45)') BlockingFromIso

, Y.x.value('data(.)', 'varchar(5000)') AS BlockingSQLcmd

, Y.x.value('@waitresource', 'nvarchar(45)') BlockingObject

, DB_NAME(X.x.value('@currentdb', 'int')) Blockeddb

, X.x.value('@waitresource', 'nvarchar(45)') BlockedObject

, X.x.value('@lockMode', 'nvarchar(45)') BlockedBlockLockMode

, X.x.value('@hostname', 'nvarchar(45)') BlockedFromHOst

, X.x.value('@loginname', 'nvarchar(45)') BlockedFromLogin

, X.x.value('@clientapp', 'nvarchar(100)') BlockedClientApp

, X.x.value('@isolationlevel', 'nvarchar(45)') BlockedFromIso

, X.x.value('data(.)', 'varchar(5000)') AS BlockedSQLcmd

FROM dbo.BlockingEventsXML a

CROSS APPLY EventData.nodes('EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process')

AS X ( x )

CROSS APPLY EventData.nodes('EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process')

AS Y ( x )

--ORDER BY EventTime desc