读取死锁扩展事件的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字段复制/粘贴到一个新文件中，并使用"File“直接将其读入SSMS或Sentry计划资源管理器。
环形缓冲链路：
https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_缓冲区-扩展中的目标事件/
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/
文件：
https://www.mssqltips.com/sqlservertip/3636/query-data-from-extended-events-in-sql-server/
@@version = Microsoft 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)
回答 1
Database Administration用户
发布于 2017-07-31 21:38:06
这似乎是可行的：
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 deschttps://dba.stackexchange.com/questions/166773
复制相似问题
腾讯云开发者
