Obtenir la liste des deadlocks rétrospectivement

Publié le par

Voici une requête qui permet de retrouver la liste des deadlocks qui ont eu lieu dans une instance SQL Server.
Elle se base sur la session d’événements étendus par défaut de SQL Server 2008…

Les événements étendus sont une nouvelle fonctionnalité introduite avec SQL Server, qui permet de tracer des événements suivant les paramètres de la session.
Les données peuvent ensuite être dépouillées à l’aide des données exposées de façon structurée sous la forme de documents XML.

Mais il en existe une, démarrée par défaut, qui trace certains événements assez sévères, et qui permet en outre de retrouver les deadlocks et les requêtes qui y ont participé de façon rétrospective, à l’aide de la requête suivante :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
;WITH
  -- Récupération des documents XML capturés par la session
  xml_event_payload AS
  (
    SELECT    CAST(T.target_data AS xml) AS data
    FROM    sys.dm_xe_session_targets AS T
    INNER JOIN  sys.dm_xe_sessions AS S
          ON S.address = T.event_session_address
    WHERE    S.name = 'system_health'
  ),
  prepare AS -- Extraction des événements de deadlock
  (
    SELECT    ED.event_xml.value('(./data/value)[1]', 'varchar(max)') AS deadlock_data
    FROM    xml_event_payload AS XEP
    CROSS APPLY  data.nodes('/RingBufferTarget/event') AS ED (event_xml)
    WHERE    ED.event_xml.value('./@name', 'varchar(32)') = 'xml_deadlock_report'
  ),
  well_form_xml_doc AS -- Formation correcte du document XML (mal formé par défaut)
  (
    SELECT  REPLACE(REPLACE(deadlock_data, '<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>') AS deadlock_data
    FROM  prepare
  ),
  del_victim_list AS -- Formation correcte du document XML (mal formé par défaut)
  (
    SELECT  CAST(STUFF(deadlock_data, PATINDEX('%<victim-list>%', deadlock_data), PATINDEX('%</victim-list>%', deadlock_data) + 14 - PATINDEX('%<victim-list>%', deadlock_data), '') AS xml) AS deadlock_data
      , ROW_NUMBER() OVER(ORDER BY deadlock_data) AS n
    FROM  well_form_xml_doc
  )    
SELECT    n
    , D.name AS database_name
    , DC.deadlock_characteristics.value('./@lasttranstarted', 'datetime') AS occurence_date_time
    , DC.deadlock_characteristics.value('(./inputbuf)[1]', 'varchar(max)') AS query
    , deadlock_data
FROM    del_victim_list
CROSS APPLY  deadlock_data.nodes('/deadlock-list/deadlock/process-list/process') AS DC (deadlock_characteristics)
INNER JOIN  sys.databases AS D ON D.database_id = DC.deadlock_characteristics.value('./@currentdb', 'int')
WHERE    DC.deadlock_characteristics.value('./@lasttranstarted', 'datetime') > '20100823 00:00:00.000'
ORDER BY  del_victim_list.n

Chaque événement capturé par la session consiste en un document XML.
La requête triture les données XML retournées par la session, car ceux-ci sont mal formés pour les deadlocks.

La colonne n retourne une numéro arbitraire qui correspond à une occurence de deadlock.
Dès lors toute les requêtes ayant le même numéro ont participé au deadlock.

En cliquant dans la colonne deadlock_data, on obtient le document XML généré par l’occurence d’un deadlock, avec quelques informations en plus, comme le niveau d’isolation, le nom de l’application et de la transaction, …

Bon backtrack !

ElSuket.

Laisser un commentaire