Récupérer les graphes des deadlocks rétrospectivement : les requêtes adéquates aux versions

Publié le par

Faisant suite au petit billet que j’ai publié il y quelques temps et qui a donc un peu pris la poussière, voici les requêtes qui permettent de récupérer les deadlocks qui se sont produits.

Quelle que soit la version de SQL Server, les caractéristiques des 256 dernières situations de blocage qui ont conduit à l’annulation de la transaction détenant le moins de ressources (puisque la moins « chère » à annuler) sont retenus par une cible de type anneau de mémoire (qui fonctionne sur le principe d’un FIFO) par une session d’évènements étendus que l’on pourrait qualifier de « système », et nommée system_health.

On peut s’en tenir au document XML exposé par ces requêtes pour comprendre quelles ressources étaient en jeu, et procéder à l’optimisation adéquate qui permettra d’éviter que de telles situations se reproduisent. Néanmoins, comme un schéma vaut souvent mieux que mille mots, on peut aussi récupérer le document XML de chaque ligne généré par ces requêtes, et le sauvegarder dans un fichier de type texte, dont on changera l’extension par « .xdl » : ceci permet de voir la situation de blocage sous la forme d’un graphe; ce type de fichier s’ouvre avec SQL Server Management Studio (cf. Livre SQL Server 2014)

Ces graphes ont évolué avec les sorties des opus de SQL Server, si bien que, par exemple, la requête qui permet de les récupérer sous SQL Server 2008R2 ne fonctionne pas pour les versions suivantes de SQL Server. En revanche, la structure de ce document est stable depuis SQL Server 2012.

Voici donc les requêtes :

1
2
3
4
5
6
7
8
9
10
11
12
13
-- SQL Server 2008
SELECT          E.e.value('@timestamp', 'datetime') AS occurence_date_time
                , CAST(E.e.value('(data/value)[1]', 'varchar(max)') AS xml) AS deadlock_graph
                , GETDATE() AS local_date_time
FROM            (
                        SELECT          CAST(target_data AS xml) AS target_data
                        FROM            sys.dm_xe_session_targets AS ST
                        INNER JOIN      sys.dm_xe_sessions AS S
                                                ON S.address = ST.event_session_address
                        WHERE           S.name = 'system_health'
                ) AS TD
CROSS APPLY     TD.target_data.nodes ('//RingBufferTarget/event') AS E(e)
WHERE           E.e.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--SQL Server 2008 R2
SELECT  occurence_date_time
        , CAST(T.event_data.value('(event/data/value)[1]', 'varchar(max)') AS xml) AS deadlock_graph
        , GETDATE() AS local_date_time
FROM    (
                SELECT          XDR.xdr.query('.') AS event_data
                                , XDR.xdr.value('@timestamp', 'datetime') AS occurence_date_time
                FROM            (
                                        -- Cast the target_data to XML
                                        SELECT          CAST(target_data AS XML) AS target_data
                                        FROM            sys.dm_xe_session_targets AS ST
                                        JOIN            sys.dm_xe_sessions AS S
                                                                ON s.address = st.event_session_address
                                        WHERE           S.name = 'system_health'
                                        AND             ST.target_name = 'ring_buffer'
                                ) AS TD -- Split out the Event Nodes
                CROSS APPLY     TD.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XDR(xdr)
        ) AS T(event_data, occurence_date_time)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- SQL Server 2012, 2014, 2016
SELECT  SRC.occurence_date_time
        , SRC.deadlock_graph.query('(event/data/value/deadlock)[1]') AS deadlock_graph
        , GETDATE() AS local_date_time
FROM    (
                SELECT          XDR.xdr.query('.') AS deadlock_graph
                                , XDR.xdr.value('@timestamp', 'datetime') AS occurence_date_time
                FROM            (
                                        SELECT          CAST(ST.target_data AS xml) AS target_data
                                        FROM            sys.dm_xe_session_targets AS ST
                                        INNER JOIN      sys.dm_xe_sessions AS S
                                                                ON S.address = ST.event_session_address
                                        WHERE           S.name = 'system_health'
                                        AND             ST.target_name = 'ring_buffer'
                                ) AS TD(target_data)
                CROSS APPLY     TD.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XDR(xdr)
        ) AS SRC;

Bonne lecture et déblocage !

ElSüket.

Laisser un commentaire