Systemhealth xevents : deadlock reports

Cette requête vous permet d’extraire les évènements « deadlock report » de la session d’évènements étendus « system health » :

/*-------------------------------------------------------------------
[SCRIPT] Extract deadlock reports from system_health xevent file
[DATABASE] 
[DESCRIPTION] 
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ]  20200112
-------------------------------------------------------------------*/
with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
   from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where object_name = 'xml_deadlock_report'
)
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc;

/*-------------------------------------------------------------------

[SCRIPT] Extract deadlock reports from system_health xevent file

[DATABASE]

[DESCRIPTION]

[MAJ PAR] DATAFLY - Arian Papillon

[DATEMAJ] 20200112

-------------------------------------------------------------------*/

with XmlDeadlockReports as

(

select convert(xml, event_data) as EventData

from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)

where object_name = 'xml_deadlock_report'

)

select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,

EventData.query('event/data/value/deadlock') as XdlFile

from XmlDeadlockReports

order by TimeStamp desc;

La colonne « xdl file » contient très exactement le contenu d’un fichier de rapport .xdl : il suffit donc d’enregistrer le xml avec cette extension et de rouvrir ce fichier avec SSMS pour avoir le deadlock graph au format graphique.

Voir : http://blog.datafly.pro/post/2020/02/13/Avez-vous-des-deadlocks