Extended Events - Pro SQL Server Internals - page 554

(
action
(
sqlserver.session_id
,sqlserver.plan_handle
,sqlserver.sql_text
)
where(sqlserver.is_system=0)
)
add target
package0.event_file
(set filename='c:\ExtEvents\TempDB_Spiils.xel',max_file_size=25),
add target
package0.ring_buffer
(set max_memory=4096)
with
(
max_memory=4096KB
,event_retention_mode=allow_single_event_loss
,max_dispatch_latency=15 seconds
,track_causality=off
,memory_partition_mode=none
,startup_state=off
);

As already mentioned, for asynchronous targets, SQL Server stores collected events in a set of memory buffers,
using multiple buffers to separate the collection and processing of events. The number of buffers and their size
depends on the max_memory and memory_partition_mode settings. SQL Server uses the following algorithm, rounding
the buffer size up to the next 64KB boundary:
memory_partition_mode = none : SQL Server uses three central buffers with the size of
max_memory / 3 rounded up to next 64KB boundary. For example, a max_memory of 4000KB
would create three buffers of 1344KB each, regardless of the server configuration.
memory_partition_mode = per_node : SQL Server creates a separate set of three buffers
each per NUMA node. For example, on the server with two NUMA nodes, a max_memory of
4000KB would create six buffers, three per node at a size of 704KB per buffer.
memory_partition_mode = per_cpu : SQL Server creates the number of buffers based on this
formula: 2.5 * (number of CPUs) , and partitions them on a per-cpu basis. For example,
on the server with 20 CPUs, a max_memory of 4000KB would create 50 buffers of 128KB each.
Partitioning by NUMA node or CPU allows multiple CPUs to store events in a separate set of buffers, which helps
reduce contentions and, therefore, the performance impact of Extended Events sessions that collect a very large
number of events. There is a caveat, however. An event needs to be able to fit into the buffer in order to be collected.
As you may have noticed, buffer partitioning increases the number of buffers and this reduces their size. This is
usually not a problem because most of the events are relatively small. However, it is also possible to define a very large
event that would not fit into the buffer. Make sure that you increase max_memory when you partition events on the
server with a large number of NUMA nodes and/or CPUs.

■ You can examine the largest_event_dropped_size column of the sys.dm_xe_sessions view to check to
see if the buffers are big enough to fit the events.

Note