{"id":45477,"date":"2026-07-08T16:59:04","date_gmt":"2026-07-08T14:59:04","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=45477"},"modified":"2026-07-08T16:59:07","modified_gmt":"2026-07-08T14:59:07","slug":"measuring-the-real-performance-cost-of-sql-server-xe-buffers","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/measuring-the-real-performance-cost-of-sql-server-xe-buffers\/","title":{"rendered":"Measuring the real performance cost of SQL Server XE buffers"},"content":{"rendered":"\n

Context<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

Two DMVs are enough for this analysis:<\/p>\n\n\n\n

sys.dm_xe_sessions<\/code> for buffer state, data volume and dropped events
sys.dm_os_wait_stats<\/code> for the wait types produced by the XE <\/p>\n\n\n\n

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. <\/p>\n\n\n\n

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<\/em>, all others are disabled even the built-in ones).<\/p>\n\n\n\n

Monitoring with sys.dm_xe_sessions<\/h2>\n\n\n
\nSELECT \n    s.name AS session_name,\n    s.dropped_event_count,\n    s.dropped_buffer_count,\n    s.buffer_policy_desc,\n    CAST(ROUND(s.total_buffer_size \/ 1024.0 \/ 1024.0, 2) AS FLOAT) AS total_buffer_size_mb,\n    s.total_regular_buffers,\n    CAST(ROUND(s.regular_buffer_size \/ 1024.0 \/ 1024.0, 2) AS FLOAT) AS regular_buffer_size_mb,\n    s.buffer_processed_count,\n    CAST(ROUND(s.total_bytes_generated \/ 1024.0 \/ 1024.0, 2) AS FLOAT) AS total_bytes_generated_mb,\n    DATEDIFF(MINUTE, s.create_time, GETDATE()) AS session_age_minutes,\n    CAST(ROUND(s.total_bytes_generated \/ 1024.0 \/ 1024.0 \n         \/ NULLIF(DATEDIFF(MINUTE, s.create_time, GETDATE()), 0), 2) AS FLOAT) AS bytes_generated_mb_per_minute,\n    CAST(ROUND(s.dropped_event_count * 1.0 \n         \/ NULLIF(DATEDIFF(MINUTE, s.create_time, GETDATE()), 0), 2) AS FLOAT) AS dropped_event_count_per_minute\nFROM sys.dm_xe_sessions s\nWHERE s.name = '<XE_NAME>';\n<\/pre><\/div>\n\n\n
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:<\/p>\n\n\n\n
Column<\/strong><\/td>What an increase means<\/strong><\/td><\/tr>
dropped_event_count<\/code><\/td>The server sacrificed events because it could not keep up with the volume<\/td><\/tr>
dropped_buffer_count<\/code><\/td>Entire buffers were lost, usually because the target cannot drain them fast enough<\/td><\/tr>
buffer_processed_count<\/code><\/td>Normal activity<\/td><\/tr>
bytes_generated_mb_per_minute<\/code><\/td>The session captures more data than expected for its age<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
buffer_policy_desc<\/code> is worth a look too: drop_event<\/code> means the session is asynchronous and accepts losing events under pressure, block<\/code> means NO_EVENT_LOSS<\/code> was configured and application threads will wait instead of dropping anything. More on that below.<\/p>\n\n\n\n
Monitoring the XE wait types<\/h2>\n\n\n\n
There are more XE-related wait types than this, but after testing I only kept the three that actually tell you something:<\/p>\n\n\n
\nSELECT \n    wait_type,\n    waiting_tasks_count,\n    wait_time_ms,\n    max_wait_time_ms,\n    CAST(wait_time_ms * 1.0 \/ NULLIF(waiting_tasks_count, 0) AS DECIMAL(10,2)) AS avg_wait_time_ms\nFROM sys.dm_os_wait_stats\nWHERE wait_type IN (\n    'XE_TIMER_EVENT',\n    'XE_DISPATCHER_WAIT',\n    'PREEMPTIVE_XE_DISPATCHER'\n)\nAND waiting_tasks_count > 0\nORDER BY wait_time_ms DESC;\n<\/pre><\/div>\n\n\n
XE_TIMER_EVENT<\/code> <\/a>is the dispatcher thread waiting for the next flush cycle defined by MAX_DISPATCH_LATENCY<\/code>. It is always present and always harmless.<\/p>\n\n\n\n
XE_DISPATCHER_WAIT<\/code> <\/a>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.<\/p>\n\n\n\n
PREEMPTIVE_XE_DISPATCHER<\/code> <\/a>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.<\/p>\n\n\n\n
A healthy reference point<\/h2>\n\n\n\n
On my lab, the system_health<\/code> <\/a>session has been running for about 132 days:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
4 GB in 132 days, nothing dropped. On the wait side, XE_DISPATCHER_WAIT<\/code> averages around 57 seconds per wait, meaning the dispatcher spends almost a minute idle between two buffers, and PREEMPTIVE_XE_DISPATCHER<\/code> sits at 0 ms. That is what an XE session that nobody notices looks like.<\/p>\n\n\n\n
Building a session that hurts<\/h2>\n\n\n\n
Now the opposite. This session combines everything you should not do:<\/p>\n\n\n
\nCREATE EVENT SESSION [XE_STRESS_TEST] ON SERVER\nADD EVENT sqlserver.sql_statement_completed (\n    ACTION (\n        sqlserver.sql_text,\n        sqlserver.query_hash,\n        sqlserver.query_plan_hash,\n        sqlserver.plan_handle,\n        sqlserver.username,\n        sqlserver.database_name,\n        sqlserver.client_hostname,\n        package0.collect_system_time\n    )\n),\nADD EVENT sqlserver.query_post_execution_showplan (\n    ACTION (\n        sqlserver.sql_text,\n        sqlserver.database_name,\n        package0.collect_system_time\n    )\n)\nADD TARGET package0.event_file (\n    SET filename = N'C:\\XE\\XE_STRESS_TEST.xel',\n        max_file_size = 10,\n        max_rollover_files = 999\n)\nWITH (\n    MAX_MEMORY = 512 KB,\n    EVENT_RETENTION_MODE = NO_EVENT_LOSS,\n    MAX_DISPATCH_LATENCY = 1 SECONDS,\n    MAX_EVENT_SIZE = 10240 KB,\n    MEMORY_PARTITION_MODE = NONE,\n    TRACK_CAUSALITY = ON,\n    STARTUP_STATE = OFF\n);\n<\/pre><\/div>\n\n\n
Why each configuration option is a bad idea:<\/p>\n\n\n\n