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:
@@version = Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)