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
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
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 :
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
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 :
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
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 !
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
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.
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;
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