read from extended events written to ring buffer

SELECT CAST(target_data as xml) AS targetdata
INTO #capture_waits_data
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes
ON xes.address = xet.event_session_address
WHERE xes.name = ‘xeventname’
AND xet.target_name = ‘ring_buffer’;

SELECT
xed.event_data.value(‘(action[@name=”username”]/value)[1]’, ‘varchar(100)’) AS username,
xed.event_data.value(‘(action[@name=”database_name”]/value)[1]’, ‘varchar(100)’) AS dbname,
xed.event_data.value(‘(data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS statement,
xed.event_data.value(‘(action[@name=”collect_system_time”]/value)[1]’, ‘datetime’) AS date
FROM #capture_waits_data
CROSS APPLY targetdata.nodes(‘//RingBufferTarget/event’) AS xed (event_data);

 

http://www.brentozar.com/archive/2015/01/query-extended-events-target-xml/

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.