1

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!

5
  • 4
    Are you aware that deadlock reports are already captured by the system_health XE trace, which has both ring buffer and rollover file targets?
    – Dan Guzman
    Nov 9, 2023 at 18:43
  • Is the ring buffer not already a FIFO?
    – Ben Thul
    Nov 9, 2023 at 22:20
  • @DanGuzman - thanks, I wasn't aware of that, I'll look into that!
    – Kevin Pope
    Nov 9, 2023 at 22:30
  • @BenThul - Perhaps there needs to be a setting set somewhere that I haven't done, but when I query the ring buffer I see deadlocks that occurred in October, but not the deadlock that I got a warning email about from this morning, so at the very least my set up doesn't seem to be respecting the "FO" part of FIFO.
    – Kevin Pope
    Nov 9, 2023 at 22:32
  • Pretty sure that it's a FIFO but there are reasons why the deadlock you expect to be there wouldn't be. XE can allow for event loss in the service of speed. Check the options under event_session_options in the documentation.
    – Ben Thul
    Nov 10, 2023 at 6:29

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.