Extraire le code des requêtes d’un verrou mortel traqué par un graphe « deadlock » du profiler

L’outil profiler de SQL Server permet de partir à la chasse au verrous mortel et peut récupérer le graphe du verrouillage des éléments concurrents et notamment les données technique de la victime. Mais ce graphe est en fait un document XML contenant toutes les informations sur les processus en jeu, la victime et les survivants. Il est alors intéressant d’en extraire les requêtes fautives afin de corriger son code ou d’indexer les tables, quelques un des moyens de se débarrasser des interblocages…

Pour détecter les verrous mortels, il suffit de demander au profiler de tracer les événements de type « deadlock » de la catégorie « locks », à savoir :

  • Deadlock graph
  • Lock: Deadlock
  • Lock Deadlock Chain
Événements à cocher pour auditer les verrous mortels de SQL Server

Événements à cocher pour auditer les verrous mortels de SQL Server

Une foi enregistrés, les données du profiler peuvent être vues par le client graphique sous cette forme :

Lecture des informations de verrous mortels dans le client graphique du profiler de SQL Server

Lecture des informations de verrous mortels dans le client graphique du profiler de SQL Server

Néanmoins, il faut passer à l’aide de sa souris sur chacune des bulles représentant les processus pour obtenir le texte des requêtes en jeu. Les rectangles visualisant les verrous, les pattes formant le graphe, qui lorsqu’il présente un cycle contient un interblocage.

Dès lors, on peut récupérer les fichiers générés sous forme SQL via la fonction table sys.fn_trace_gettable dont le premier argument est le chemin de stockage des fichiers de trace du profiler et le second le numéro du fichier concerné. On peut aussi tous les obtenir en mettant le mot clef DEFAULT.
L’événement de graphe de verrouillage étant de classe 148, il suffit ensuite de filtrer sur ces lignes.
Exemple :

SELECT *
FROM   sys.fn_trace_gettable('C:\SQL_TRACE\20180219_14H30_DEADLOCK_.trc', default)

La colonne TextData de cette table virtuelle contient alors le XML donnant toutes les informations sur le verrou mortel. En voici un exemple :

Document XML montrant toutes les informations d'un verrou mortel survenu dans SQL Server

Document XML montrant toutes les informations d’un verrou mortel survenu dans SQL Server

Avec une requête mêlant du SQL et du XML via XQuery et XPath, il est possible d’extraire ce qui nous intéresse, à savoir, le texte des requêtes en jeu ainsi qu’une indication disant quelle est la victime et les survivants. Et comme il n’y a pas qu’un seul interblocage en général, alors on les identifie par un numéro. la requête au final ressemble à cela :

WITH
TX AS
(
SELECT StartTime, CAST(TextData AS xml) AS TextData
FROM   sys.fn_trace_gettable('C:\! FB\Rhenus\2018-02-19\20180219_14H30_DEADLOCK_.trc', DEFAULT)
WHERE  EventClass = 148
),
TVM AS
(
SELECT StartTime,
       v.VALUE('(./inputbuf)[1]','nvarchar(max)') AS Query,
       i.VALUE('(./deadlock/@victim)[1]','varchar(32)') AS ProcessVictim,
       v.VALUE('(./@id)[1]','varchar(32)') AS ProcessID
FROM  TX
      CROSS APPLY TextData.nodes('/deadlock-list') AS X(i)
      CROSS APPLY TextData.nodes('/deadlock-list/deadlock/process-list/process') AS V(v)
),
TVV AS
(
SELECT DENSE_RANK() OVER (ORDER BY StartTime) AS ID,
       StartTime,
       Query,
       CASE WHEN ProcessVictim = ProcessID THEN 'Victim!' ELSE 'Alive' END AS FinalState
FROM TVM
),
TQV AS
(
SELECT ID, Query
FROM   TVV
WHERE  FinalState = 'Victim!'
)
SELECT TVV.ID, TVV.StartTime, TVV.Query,
       CASE WHEN TQV.ID IS NOT NULL THEN 'Victim!' ELSE 'Alive' END AS FinalState
FROM   TVV
       LEFT OUTER JOIN TQV
             ON TVV.ID = TQV.ID AND TVV.Query = TQV.Query
ORDER BY 1;

Au final une telle requête permet de présenter les informations suivantes :

Visualisation des requêtes en jeu dans un verrou mortel

Visualisation des requêtes en jeu dans un verrou mortel


Notez au passage le colonne « FinalState » indiquant quelle requête a été victime et celles ayant survécus.

Le code ! Le code ! Le code ! Le code ! Le code ! Le code ! Le code ! Le code ! Le code ! Le code !

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL
Server

2 réflexions au sujet de « Extraire le code des requêtes d’un verrou mortel traqué par un graphe « deadlock » du profiler »

  1. Avatar de SQLproSQLpro Auteur de l’article

    Pas d’erreur de votre côté, mais simplement developpez.com qui déconne depuis des années en modifiant le code que l’on poste… En effet, VALUE ne devrait pas être en majuscule mais en minuscule.

    WITH
    TX AS
    (
    SELECT StartTime, CAST(TextData AS xml) AS TextData
    FROM   sys.fn_trace_gettable('C:\! FB\Rhenus\2018-02-19\20180219_14H30_DEADLOCK_.trc', DEFAULT)
    WHERE  EventClass = 148
    ),
    TVM AS
    (
    SELECT StartTime,
           v.value('(./inputbuf)[1]','nvarchar(max)') AS Query,
           i.value('(./deadlock/@victim)[1]','varchar(32)') AS ProcessVictim,
           v.value('(./@id)[1]','varchar(32)') AS ProcessID
    FROM  TX
          CROSS APPLY TextData.nodes('/deadlock-list') AS X(i)
          CROSS APPLY TextData.nodes('/deadlock-list/deadlock/process-list/process') AS V(v)
    ),
    TVV AS
    (
    SELECT DENSE_RANK() OVER (ORDER BY StartTime) AS ID,
           StartTime,
           Query,
           CASE WHEN ProcessVictim = ProcessID THEN 'Victim!' ELSE 'Alive' END AS FinalState
    FROM TVM
    ),
    TQV AS
    (
    SELECT ID, Query
    FROM   TVV
    WHERE  FinalState = 'Victim!'
    )
    SELECT TVV.ID, TVV.StartTime, TVV.Query,
           CASE WHEN TQV.ID IS NOT NULL THEN 'Victim!' ELSE 'Alive' END AS FinalState
    FROM   TVV
           LEFT OUTER JOIN TQV
                 ON TVV.ID = TQV.ID AND TVV.Query = TQV.Query
    ORDER BY 1;
  2. Avatar de HurbainJHurbainJ

    Bonjour,

    Lorsque j’essaye d’exécuter la requête mêlant du SQL et du XML via XQuery et XPath, j’obtiens l’erreur suivante:
    « Msg 4121, Niveau 16, État 1, Ligne 1
    Impossible de trouver la colonne « v » la fonction définie par l’utilisateur ou l’agrégat « v.VALUE ». Le nom pourrait également être ambigu. »

    J’ai beau chercher, je ne trouve pas le problème (je n’ai pas l’habitude de manipuler des données XML en SQL)

    Ou alors, cela pourrait-il être un problème de version (SQL Server 2016 Enterprise dans mon cas) ?

    Merci

    Jérôme

Laisser un commentaire