Reading the Ring Buffer in Extended Events
The much-maligned ring buffer target in Extended Events offers a simple, reliable place to store transient events. While it’s pretty easy to use the “Live Data” view in SQL Server Management Studio to view events as they happen, it can be helpful to programmatically extract data from the ring buffer manually.
The ring buffer target briefly holds event data in memory. This target can manage events in one of two modes.
The first mode is strict first-in first-out (FIFO), where the oldest event is discarded when all the memory allocated to the target is used. In this mode (the default), the occurrence_number option is set to 0.
The second mode is per-event FIFO, where a specified number of events of each type is kept. In this mode, the oldest events of each type are discarded when all the memory allocated to the target is used. You can configure the occurrence_number option to specify the number of events of each type to keep.
Not a ring buffer target, but still interesting enough!
The code below offers a quick way to parse the ring buffer with a time-based filter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
DECLARE @ExtendedEventsSessionName sysname = N'<session_name_here>'; DECLARE @StartTime datetimeoffset; DECLARE @EndTime datetimeoffset; DECLARE @Offset int; DROP TABLE IF EXISTS #xmlResults; CREATE TABLE #xmlResults ( xeTimeStamp datetimeoffset NOT NULL , xeXML XML NOT NULL ); SET @StartTime = DATEADD(HOUR, -4, GETDATE()); --modify this to suit your needs SET @EndTime = GETDATE(); SET @Offset = DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()); SET @StartTime = DATEADD(MINUTE, @Offset, @StartTime); SET @EndTime = DATEADD(MINUTE, @Offset, @EndTime); SELECT StartTimeUTC = CONVERT(varchar(30), @StartTime, 127) , StartTimeLocal = CONVERT(varchar(30), DATEADD(MINUTE, 0 - @Offset, @StartTime), 120) , EndTimeUTC = CONVERT(varchar(30), @EndTime, 127) , EndTimeLocal = CONVERT(varchar(30), DATEADD(MINUTE, 0 - @Offset, @EndTime), 120); DECLARE @target_data xml; SELECT @target_data = CONVERT(xml, target_data) FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address WHERE s.name = @ExtendedEventsSessionName AND t.target_name = N'ring_buffer'; ;WITH src AS ( SELECT xeXML = xm.s.query('.') FROM @target_data.nodes('/RingBufferTarget/event') AS xm(s) ) INSERT INTO #xmlResults (xeXML, xeTimeStamp) SELECT src.xeXML , [xeTimeStamp] = src.xeXML.value('(/event/@timestamp)[1]', 'datetimeoffset(7)') FROM src; SELECT [TimeStamp] = CONVERT(varchar(30), DATEADD(MINUTE, 0 - @Offset, xr.xeTimeStamp), 120) , xr.xeXML FROM #xmlResults xr WHERE xr.xeTimeStamp >= @StartTime AND xr.xeTimeStamp<= @EndTime ORDER BY xr.xeTimeStamp; |
Let me know if you have any questions about the code above!
See the rest of our series of posts on Extended Events.