Measuring the real performance cost of SQL Server XE buffers

Context

Extended Events have a reputation for being lightweight, and most of the time they are. However, poorly configured setups can heavily degrade your SQL Server Extended Events performance, stalling application threads and keeping a disk busy for minutes after your workload has ended. The events you choose to capture matter just as much as the session settings: some events, like the showplan ones, carry a heavy cost by design regardless of how you configure the rest of the session. I wanted to see what all of this looks like from the DMV side, so I built the worst XE session I could think of, threw a StackOverflow workload at it, and compared the numbers with a healthy session.

Two DMVs are enough for this analysis:

sys.dm_xe_sessions for buffer state, data volume and dropped events
sys.dm_os_wait_stats for the wait types produced by the XE

Keep in mind that both are in-memory structures. The counters in sys.dm_xe_sessions accumulate since the session started (create_time), and sys.dm_os_wait_stats accumulates since the instance started but everything resets after a restart.

Also note that wait statistics are instance-wide: if several XE sessions run at the same time, the XE wait types aggregate all of them (that’s why we will focus on one specific XE called XE_STRESS_TEST, all others are disabled even the built-in ones).

Monitoring with sys.dm_xe_sessions

SELECT 
    s.name AS session_name,
    s.dropped_event_count,
    s.dropped_buffer_count,
    s.buffer_policy_desc,
    CAST(ROUND(s.total_buffer_size / 1024.0 / 1024.0, 2) AS FLOAT) AS total_buffer_size_mb,
    s.total_regular_buffers,
    CAST(ROUND(s.regular_buffer_size / 1024.0 / 1024.0, 2) AS FLOAT) AS regular_buffer_size_mb,
    s.buffer_processed_count,
    CAST(ROUND(s.total_bytes_generated / 1024.0 / 1024.0, 2) AS FLOAT) AS total_bytes_generated_mb,
    DATEDIFF(MINUTE, s.create_time, GETDATE()) AS session_age_minutes,
    CAST(ROUND(s.total_bytes_generated / 1024.0 / 1024.0 
         / NULLIF(DATEDIFF(MINUTE, s.create_time, GETDATE()), 0), 2) AS FLOAT) AS bytes_generated_mb_per_minute,
    CAST(ROUND(s.dropped_event_count * 1.0 
         / NULLIF(DATEDIFF(MINUTE, s.create_time, GETDATE()), 0), 2) AS FLOAT) AS dropped_event_count_per_minute
FROM sys.dm_xe_sessions s
WHERE s.name = '<XE_NAME>';

Since the counters are cumulative, a single snapshot tells you very little. What you want to know is whether they increase between two runs of the query. In practice:

buffer_policy_desc is worth a look too: drop_event means the session is asynchronous and accepts losing events under pressure, block means NO_EVENT_LOSS was configured and application threads will wait instead of dropping anything. More on that below.

Monitoring the XE wait types

There are more XE-related wait types than this, but after testing I only kept the three that actually tell you something:

SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    CAST(wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0) AS DECIMAL(10,2)) AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'XE_TIMER_EVENT',
    'XE_DISPATCHER_WAIT',
    'PREEMPTIVE_XE_DISPATCHER'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

XE_TIMER_EVENT is the dispatcher thread waiting for the next flush cycle defined by MAX_DISPATCH_LATENCY. It is always present and always harmless.

XE_DISPATCHER_WAIT is the dispatcher waiting for buffers to process. Counter-intuitively, a high average is good news: the dispatcher spends its time waiting for work. A very low average means it never gets to rest between flushes.

PREEMPTIVE_XE_DISPATCHER occurs when the dispatcher switches to preemptive mode to execute an operation outside of SQLOS control, typically writing event data to disk through an OS call. It shows up under high XE load, during OS interactions, or when the storage cannot absorb the writes. On a healthy instance this stays at zero. When it starts growing, your XE session has become a real workload for the server.

A healthy reference point

On my lab, the system_health session has been running for about 132 days:

4 GB in 132 days, nothing dropped. On the wait side, XE_DISPATCHER_WAIT averages around 57 seconds per wait, meaning the dispatcher spends almost a minute idle between two buffers, and PREEMPTIVE_XE_DISPATCHER sits at 0 ms. That is what an XE session that nobody notices looks like.

Building a session that hurts

Now the opposite. This session combines everything you should not do:

CREATE EVENT SESSION [XE_STRESS_TEST] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.query_hash,
        sqlserver.query_plan_hash,
        sqlserver.plan_handle,
        sqlserver.username,
        sqlserver.database_name,
        sqlserver.client_hostname,
        package0.collect_system_time
    )
),
ADD EVENT sqlserver.query_post_execution_showplan (
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        package0.collect_system_time
    )
)
ADD TARGET package0.event_file (
    SET filename = N'C:\XE\XE_STRESS_TEST.xel',
        max_file_size = 10,
        max_rollover_files = 999
)
WITH (
    MAX_MEMORY = 512 KB,
    EVENT_RETENTION_MODE = NO_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 1 SECONDS,
    MAX_EVENT_SIZE = 10240 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = OFF
);

Why each configuration option is a bad idea:

• query_post_execution_showplan captures the full XML execution plan of every single query. A plan can weigh several hundred KB, sometimes a couple of MB. Microsoft documents this event as having performance overhead (link).
• MAX_MEMORY = 512 KB gives the session a tiny buffer pool that fills up after a handful of events once plans are involved, so the dispatcher flushes constantly.
• NO_EVENT_LOSS tells SQL Server that losing an event is not acceptable. The consequence is that any thread firing an event while all buffers are full has to wait. Your queries pay for the XE session.
• MAX_DISPATCH_LATENCY = 1 SECONDS forces a flush cycle every second no matter what.
• TRACK_CAUSALITY = ON adds a GUID and a sequence number to every event, which costs a bit of CPU and space each time (link to the documentation).

To create the pressure on this XE, the workload was as follows: 10 parallel SSMS sessions, each running 1 000 iterations of joins between Posts, Users and Votes on the StackOverflow database, with a variable predicate so that no plan gets reused and every execution produces a fresh showplan event (the query is not important here, the only goal is to generate workload so that the XE has something heavy to capture).

Weighting the damages

Early in the load, the session had already generated 1 GB. Nothing dropped, PREEMPTIVE_XE_DISPATCHER still at zero, but the average of XE_DISPATCHER_WAIT was down to 6 ms. The dispatcher was already working non-stop, the disk was simply keeping up so far.

A minute later the picture changed completely. 2.5 GB generated, and PREEMPTIVE_XE_DISPATCHER had jumped to over 1 168 690 ms accumulated. The dispatcher was now spending its life in preemptive mode, out of SQLOS control, waiting for the OS to complete disk writes.

Then the interesting part. The 10 load sessions finished, and the XE session kept writing. With NO_EVENT_LOSS and a 512 KB pool, a backlog of buffers had piled up in memory during the load, and the dispatcher was draining it file after file. The max_wait_time_ms of XE_DISPATCHER_WAIT climbed to 74 seconds while total_bytes_generated_mb barely moved: the disk was the bottleneck.

Eight minutes after the session started, the tally was 5.4 GB written, more than 500 rollover files of 10 MB on disk, for a workload that had ended long before.

Healthy vs stressed, side by side

Note the trap on dropped_event_count: both sessions show zero, for opposite reasons. The healthy session drops nothing because it has nothing to drop. The stressed session drops nothing because NO_EVENT_LOSS made the application threads wait instead. Looking at that counter alone, both sessions look fine. Only the wait types reveal the difference.

Final Thoughts: Protecting Your Production from XE Overhead

NO_EVENT_LOSS does not buy you capacity, it converts event loss into query latency. It has its place for a short targeted capture where completeness matters, never for a permanent session.

query_post_execution_showplan without a predicate will drown any buffer configuration. If you need to capture it, scope it to a database or an object using predicates.

An undersized MAX_MEMORY turns every couple of events into a flush, and every flush is an opportunity for the dispatcher to end up in PREEMPTIVE_XE_DISPATCHER.

And check both DMVs, not just one. sys.dm_xe_sessions tells you what happened to your data, sys.dm_os_wait_stats tells you what it cost the server. In my stress test, the first one looked almost reassuring. The second one did not.