XEvent : Deadlock

10 février 2017

Commentaires fermés

Un deadlock se produit lorsque deux processus (ou plus) se bloquent mutuellement de façon permanente. Dans ce cas, chaque processus positionne un verrou sur une ressource que le ou les autres processus tentent de verrouiller. Le Moteur de base de données SQL Server détecte automatiquement le conflit et choisit l’une des sessions comme victime. La transaction en cours se termine par une erreur, ce qui met fin à la situation de conflit.

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Expert SQL Server - XEvent : Deadlock - SQL Server - deadlock

La détection de conflit est déjà réalisée dans la trace XEvent par défaut system_health. Attention, cette liste de deadlocks n’est pas exhaustive. On est tributaire du dernier redémarrage de l’instance SQL Server et de la mémoire disponible au moment de la collecte (perte d’évènements). Pour consulter les deadlocks rétroactivement, lancer la commande suivante :

SELECT  xed.value('@timestamp', 'datetime') as Creation_Date,
        xed.query('.') AS Extend_Event
FROM    (    SELECT    CAST([target_data] AS XML) AS Target_Data
            FROM    sys.dm_xe_session_targets AS xt
                    INNER JOIN sys.dm_xe_sessions AS xs
                    ON xs.address = xt.event_session_address
            WHERE    xs.name = N'system_health'
                    AND xt.target_name = N'ring_buffer')
AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC

SELECT xed.value('@timestamp', 'datetime') as Creation_Date,

xed.query('.') AS Extend_Event

FROM ( SELECT CAST([target_data] AS XML) AS Target_Data

FROM sys.dm_xe_session_targets AS xt

INNER JOIN sys.dm_xe_sessions AS xs

ON xs.address = xt.event_session_address

WHERE xs.name = N'system_health'

AND xt.target_name = N'ring_buffer')

AS XML_Data

CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)

ORDER BY Creation_Date DESC

Pour consigner les deadlocks avec une rétention plus contrôlable, la création d’une trace XEvent passera par plusieurs étapes.

Etape 1

Nous allons créer une arborescence de répertoires sur laquelle le compte de service SQL Server aura le droit d’écrire. Le répertoire deadlock stockera les traces XEvent concernées.

Expert SQL Server - XEvent : Deadlock - SQL Server - xevent_dir

Etape 2

Création de la trace XEvent en spécifiant le nom deadlock et le chemin D:\XEvent\deadock\deadock.xel. Notez qu’il y a un rollover de défini sur 5 fichiers par défaut pour une taille limitée à 5 Mo par fichier, les traces n’étant pas supposée saturer les disques.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='deadlock')
	DROP EVENT SESSION [deadlock] ON SERVER
GO

CREATE EVENT SESSION [deadlock] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(ACTION(sqlserver.database_id,sqlserver.database_name)) 
ADD TARGET package0.event_file(SET filename=N'D:\XEvent\deadlock\deadlock.xel',max_file_size=(5))
WITH (STARTUP_STATE=OFF)
GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='deadlock')

DROP EVENT SESSION [deadlock] ON SERVER

CREATE EVENT SESSION [deadlock] ON SERVER

ADD EVENT sqlserver.xml_deadlock_report(ACTION(sqlserver.database_id,sqlserver.database_name))

ADD TARGET package0.event_file(SET filename=N'D:\XEvent\deadlock\deadlock.xel',max_file_size=(5))

WITH (STARTUP_STATE=OFF)

Etape 3

Démarrage de la trace XEvent deadlock

ALTER EVENT SESSION [deadlock]  
ON SERVER  
STATE = start;  
GO

ALTER EVENT SESSION [deadlock]

ON SERVER

STATE = start;

Consultation des deadlocks

Le détail des deadlocks peut être consulté en parsant le XML des traces XEvent générées et le résultat reste à historiser dans une table au besoin. Il est désormais possible de savoir qui a tué qui, à quelle heure avec les requêtes associées aux processus vainqueur et victime.

IF OBJECT_ID('tempdb..#Events') IS NOT NULL 
	DROP TABLE #Events

DECLARE	@TopCount Int = 100,
		@SessionName SysName = 'deadlock',
		@Target_File VARCHAR(1000),
		@Target_Dir VARCHAR(1000),
		@Target_File_WildCard VARCHAR(1000)

-- Récupérer le fichier de trace
SELECT	@Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'VARCHAR(256)')
FROM	sys.dm_xe_session_targets t
INNER	JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE	s.name = @SessionName
AND		t.target_name = 'event_file'

SET @Target_Dir = LEFT(@Target_File, LEN(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) 
SET @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel'

--SELECT @Target_File_WildCard

-- Récupérer des n Events en XML
SELECT TOP (@TopCount) CAST(event_data AS XML) AS event_data
INTO	#Events
FROM	sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F
ORDER BY File_name DESC, file_offset DESC 

SELECT  DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE())
        , event_data.value('(event/@timestamp)[1]', 'datetime')) AS log_date_time
		, event_data.query('(/event/data/value/deadlock)[1]') AS DeadLockGraph
FROM    #Events

IF OBJECT_ID('tempdb..#Events') IS NOT NULL

DROP TABLE #Events

DECLARE @TopCount Int = 100,

@SessionName SysName = 'deadlock',

@Target_File VARCHAR(1000),

@Target_Dir VARCHAR(1000),

@Target_File_WildCard VARCHAR(1000)

-- Récupérer le fichier de trace

SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'VARCHAR(256)')

FROM sys.dm_xe_session_targets t

INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address

WHERE s.name = @SessionName

AND t.target_name = 'event_file'

SET @Target_Dir = LEFT(@Target_File, LEN(@Target_File) - CHARINDEX('\', REVERSE(@Target_File)))

SET @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel'

--SELECT @Target_File_WildCard

-- Récupérer des n Events en XML

SELECT TOP (@TopCount) CAST(event_data AS XML) AS event_data

INTO #Events

FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F

ORDER BY File_name DESC, file_offset DESC

SELECT DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE())

, event_data.value('(event/@timestamp)[1]', 'datetime')) AS log_date_time

, event_data.query('(/event/data/value/deadlock)[1]') AS DeadLockGraph

FROM #Events

Deadlock Grah

Pour obtenir des détails sur le deadlock, cliquer sur la colonne DeadLockGraph :

Voici le résultat obtenu, une vue au format XML pour l’analyse du deadlock :

Pour obtenir une vue graphique, enregistrer le fichier en utilisant l’extension xdl à la place de xml par défaut.

Ouvrir le fichier DeadLockGraph1.xdl depuis SQL Mangement Studio. On obtient une vue graphique, version TV noir & blanc.

Ouvrir le fichier DeadLockGraph1.xdl depuis SQL Sentry Plan Explorer, un outil d’analyse des plans d’exécution mais pas que. On obtient toujours une vue graphique mais version TV couleur.

Trace flags

Notez que les deadlocks peuvent être également consignés dans les logs SQL Server :

Au format XML

DBCC TRACEON (1222, -1);  
GO

1 2	DBCC TRACEON (1222, -1); GO

Au format texte

DBCC TRACEON (1204, -1);  
GO

1 2	DBCC TRACEON (1204, -1); GO

Éradication des deadlocks

Les deadlocks ne sont pas une fatalité, j’ai entendu trop fois que ce phénomène était purement applicatif et donc pas de la responsabilité du DBA. C’est faux. Ce qui est vrai en revanche, c’est que la marche de manœuvre sur une solution éditeur est beaucoup plus réduite pour éliminer les deadlocks. Cela étant, on peut toujours intervenir sur l’indexation ou encore appliquer des plans guides au besoin et remonter les deadlocks relevés à l’éditeur, à titre d’exemple. Sur une application développée en interne, voici quelques axes d’optimisation à envisager :

– Design de la base de données
– Niveau d’isolation
– Séquençage objets (ordre d’appel des objets identique d’une PS à l’autre)
– Transaction la plus courte possible
– Optimisation de code
– Indexation
(…)

Pour avoir plus d’info sur la concurrence (ex : processus bloqués) :

[JSS2015] Eradiction des deadlocks from GUSS

Auteur

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Réplication - Initialisation d'un abonné par sauvegarde avec édition de la réplication

sp_whoisactive : Processus actifs sur une instance SQL Server

Tuning d'instance SQL Server

SQL Server : Compilation et recompilation

Blog