我正在尝试查询system_health扩展事件环形缓冲区中的死锁事件。到目前为止,我已经得到了如下的死锁图。我对缺少参考资料表示歉意;我丢失了从中找到此查询的页面:
SELECT
CAST(event_data.value('(event/data/value)[1]', 'nvarchar(max)') AS XML) AS DeadlockGraph
FROM
( SELECT XEvent.query('.') AS event_data
FROM
( -- Cast the target_data to XML
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets AS st
INNER JOIN sys.dm_xe_sessions AS s
ON s.address = st.event_session_address
WHERE name = N'system_health'
AND target_name = N'ring_buffer'
) AS Data
-- Split out the Event Nodes
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
) AS tab (event_data);
我还想为这些事件的时间戳添加一列。如果我运行内部查询,我会看到事件的时间戳在事件本身的XML中:
<event name="xml_deadlock_report"
package="sqlserver" id="123" version="1"
timestamp="2013-07-23T16:25:25.495Z">
如何解析此时间戳并显示它?我使用TSQL解析XML的经验有限。
我试过这个:
SELECT
CAST(event_data.value('/@timestamp', 'nvarchar(500)') AS datetime) AS [time],
CAST(event_data.value('(event/data/value)[1]', 'nvarchar(max)') AS XML) AS DeadlockGraph
但我得到了错误:
Msg 2390,级别16,状态1,第2行XQuery[tab.event_data.value()]:不支持顶层属性节点
此:
CAST(event_data.value('(event/@timestamp)', 'nvarchar(500)') AS datetime) AS [time],
产生此错误:
Msg 2389,级别16,状态1,第2行XQuery[tab.event_data.value()]:"value()"需要singleton(或空序列),找到的操作数为类型"xdt:untypedAtomic*"
更改此
CAST(event_data.value('(event/@timestamp)', 'nvarchar(500)') AS datetime) AS [time],
用这个
SELECT
event_data.value('(/event/@timestamp)[1]','VARCHAR(50)') AS TS1,
event_data.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS TS2,
...
结果:
TS1 TS2
------------------------ ----------------------------------
2013-07-23T17:49:46.072Z 2013-07-23 17:49:46.0720000 +00:00