Ring Buffer Rollover?

Question

I'm currently using the ring buffer in extended events to track deadlocks, but noticed that it stops being queryable after it hits the memory limit. When I've used the event_file option in the past I can have rollover files so this isn't as much of an issue. In various articles I've read they complain about the ring_buffer losing access to newer data, but these articles are also older (up to 10 years old at this point) and I'm curious if this has since been resolved.

For the Ring Buffer is there a notion of rollovers, an ability to order by the event time (descending), or a FIFO stack where the oldest data automatically gets dropped in favor of newer data?

We're currently running SQL Server 2017. Thanks!

Answer 1

There is a 4MB limit on the target_data column for sys.dm_xe_session_targets which is currently the only way to view the contents of a ring_buffer target. Unfortunately, this 4MB limit is only fully known when sys.dm_xe_session_targets is queried since that is when the ring target is actually serialized as XML. Due to this, the truncated data is likely to be the most recent data as the most recent data tends to be at the end of the ring buffer (the ordering is not guaranteed for events, you need to look at the timestamp of the actual event itself if you need strict ordering).

The best way to avoid this issue is to set the max_memory parameter for the ring_buffer target itself. This memory limit is completely unrelated to the MAX_MEMORY parameter that exists at the session level. The SQL Server XE team highly recommends setting this parameter to 1MB or lower (it's very difficult to provide a precise value where truncation of the target_data field won't happen since it's highly dependent on serialization).

If you cannot change the session, you can also turn the session off and then on again:

ALTER EVENT SESSION [sess] ON SERVER
STATE = STOP
GO

ALTER EVENT SESSION [sess] ON SERVER
STATE = START
GO

If you really need access to those truncated events, one thing you could try is to force the ring buffer to cycle in the case that the ring_buffer memory limit is just a bit too high. There is the sqlserver.user_event event which can be manually triggered:

ALTER EVENT SESSION [sess] ON SERVER
ADD EVENT sqlserver.user_event
GO

DECLARE @i INT = 0;
DECLARE @toIns NVARCHAR(128);
DECLARE @junkData VARBINARY(8000);
SELECT @junkData = CRYPT_GEN_RANDOM (8000);
WHILE @i < 100 --- carefully adjust this so you don't kick too much out of the buffer
BEGIN
    SELECT @toIns = N'Record: ' + CAST(@i as NVARCHAR(128))
    EXEC sp_trace_generateevent 90, @toIns, @junkData;
    SELECT @i = @i+1
END
GO

ALTER EVENT SESSION [sess] ON SERVER
DROP EVENT sqlserver.user_event