為死鎖擴展事件讀取 system_health event_file 而不是 ring_buffer

我目前正在使用這場災難通過讀取system_health擴展事件環形緩衝區來定位最近的死鎖。

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

它工作正常，但是事件似乎不會持續很長時間（比如 24 小時？）我猜這是它的“環形緩衝區”部分。現在我偶然發現了一個連結，該連結正在讀取system_health具有類似資訊的“文件”：

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%'  

該文件是否與環形緩衝區相同，但停留時間更長？使用文件有什麼缺點嗎？有一些 XML 技能的人想要轉換頂級腳本嗎？

目標是將 XdlFile 欄位複製/粘貼到新文件中，並使用“文件打開”將其直接讀入 SSMS 或 Sql Sentry Plan Explorer。

環形緩衝區連結：

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

死鎖錯誤未返回死鎖 SQL

文件：

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)

這似乎有效：

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

引用自：https://dba.stackexchange.com/questions/166773