4

I'm currently using this disaster to locate recent deadlocks by reading the system_health Extended Events ring buffer.

select top 2000000000
      XEvent.value('@timestamp', 'datetime2(3)') as CreationDateUtc,
      --
      -- Extract the <deadlock>...</deadlock> tag from within the event
      -- Todo: Surely there is a better (xml) way to do this.
      --
      substring(convert(varchar(max), XEvent.query('.')), 
          -- start
          patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))),          
          -- end
          patindex('%</deadlock%', convert(varchar(max), XEvent.query('.'))) -
              patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))) + 11 -- 11 to include for '</deadlock>'
          ) AS XdlFile
 from 
    (
      select cast (target_data as xml) as TargetData
        from sys.dm_xe_session_targets st with (nolock)
        join sys.dm_xe_sessions s with (nolock)
          on s.address = st.event_session_address
       where [name] = 'system_health'
         and st.target_name = N'ring_buffer'
    ) as Data
 cross apply TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
 order by CreationDateUtc desc

It works fine however the events don't seem to hang around very long (like 24 hours?) I guess this is the "ring buffer" part of it. Now I stumbled onto a link that was reading the system_health "file" which has similar information:

select event_data = CONVERT(XML, event_data) 
from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
where event_data like '%xml_deadlock%'  

Is this file the same as the ring buffer but just stays around longer? Any disadvantages to using a file? Does anyone with some XML skills want to convert the top script?

The goal is to copy/paste the XdlFile field into a new file and read it directly into SSMS or Sql Sentry Plan Explorer using "File Open".

Ring Buffer Links:

https://connect.microsoft.com/SQLServer/feedback/details/754115/xevents-system-health-does-not-catch-all-deadlocks#tabs

https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

http://www.sqlskills.com/blogs/jonathan/multi-victim-deadlocks/

https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/

http://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/

http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/05/01/tracking-down-deadlocks-in-sql-database.aspx

Deadlock error isn't returning the deadlock SQL

File:

https://www.mssqltips.com/sqlservertip/3636/query-data-from-extended-events-in-sql-server/

@@version = Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)

  • If you want to store deadlocks and revisit them, refer to my answer. – Kin Shah Apr 5 '17 at 22:16
4

This seems to work:

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
  • 1
    Why didn't you just do WHERE object_name = 'xml_deadlock_report' for the filter? – Jonathan Kehayias Aug 18 '17 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.