Session memory – who’s this guy named Max and what’s he doing with my memory?

SQL Server MVP Jonathan Kehayias (blog) emailed me a question last week when he noticed that the total memory used by the buffers for an event session was larger than the value he specified for the MAX_MEMORY option in the CREATE EVENT SESSION DDL. The answer here seems like an excellent subject for me to kick-off my new “401 – Internals” tag that identifies posts where I pull back the curtains a bit and let you peek into what’s going on inside the extended events engine.

In a previous post (Option Trading: Getting the most out of the event session options) I explained that we use a set of buffers to store the event data before  we write the event data to asynchronous targets. The MAX_MEMORY along with the MEMORY_PARTITION_MODE defines how big each buffer will be. Theoretically, that means that I can predict the size of each buffer using the following formula:

max memory / # of buffers = buffer size

If it was that simple I wouldn’t be writing this post.

I’ll take “boundary” for 64K Alex

For a number of reasons that are beyond the scope of this blog, we create event buffers in 64K chunks. The result of this is that the buffer size indicated by the formula above is rounded up to the next 64K boundary and that is the size used to create the buffers. If you think visually, this means that the graph of your max_memory option compared to the actual buffer size that results will look like a set of stairs rather than a smooth line. You can see this behavior by looking at the output of dm_xe_sessions, specifically the fields related to the buffer sizes, over a range of different memory inputs:

Note: This test was run on a 2 core machine using per_cpu partitioning which results in 5 buffers. (Seem my previous post referenced above for the math behind buffer count.)

input_memory_kb	total_regular_buffers	regular_buffer_size	total_buffer_size
637	5	130867	654335
638	5	130867	654335
639	5	130867	654335
640	5	196403	982015
641	5	196403	982015
642	5	196403	982015

This is just a segment of the results that shows one of the “jumps” between the buffer boundary at 639 KB and 640 KB. You can verify the size boundary by doing the math on the regular_buffer_size field, which is returned in bytes:

196403 – 130867 = 65536 bytes

65536 / 1024 = 64 KB

The relationship between the input for max_memory and when the regular_buffer_size is going to jump from one 64K boundary to the next is going to change based on the number of buffers being created. The number of buffers is dependent on the partition mode you choose. If you choose any partition mode other than NONE, the number of buffers will depend on your hardware configuration. (Again, see the earlier post referenced above.) With the default partition mode of none, you always get three buffers, regardless of machine configuration, so I generated a “range table” for max_memory settings between 1 KB and 4096 KB as an example.

start_memory_range_kb	end_memory_range_kb	total_regular_buffers	regular_buffer_size	total_buffer_size
1	191	NULL	NULL	NULL
192	383	3	130867	392601
384	575	3	196403	589209
576	767	3	261939	785817
768	959	3	327475	982425
960	1151	3	393011	1179033
1152	1343	3	458547	1375641
1344	1535	3	524083	1572249
1536	1727	3	589619	1768857
1728	1919	3	655155	1965465
1920	2111	3	720691	2162073
2112	2303	3	786227	2358681
2304	2495	3	851763	2555289
2496	2687	3	917299	2751897
2688	2879	3	982835	2948505
2880	3071	3	1048371	3145113
3072	3263	3	1113907	3341721
3264	3455	3	1179443	3538329
3456	3647	3	1244979	3734937
3648	3839	3	1310515	3931545
3840	4031	3	1376051	4128153
4032	4096	3	1441587	4324761

As you can see, there are 21 “steps” within this range and max_memory values below 192 KB fall below the 64K per buffer limit so they generate an error when you attempt to specify them.

Clarification: I got another question about this topic last week, this time from a student in one of Jonathan Kehayias' classes. Someone noticed that if you take the size of the buffer (regular_buffer_size) and divide that by 64 KB you don't get the nice even boundary that I am claiming above and that someone is wondering "What gives?". A reasonable question.

What gives is that regular_buffer_size is reporting the usable buffer space, or the space left after we subtract a small amount of memory for the overhead of managing the buffers. You'll notice that if you take any regular_buffer_size and divide it by 64 KB you'll have the same fraction left over every time: 0.99687194824219, that's the overhead.

Max approximates True as memory approaches 64K

The upshot of this is that the max_memory option does not imply a contract for the maximum memory that will be used for the session buffers (Those of you who read Take it to the Max (and beyond) know that max_memory is really only referring to the event session buffer memory.) but is more of an estimate of total buffer size to the nearest higher multiple of 64K times the number of buffers you have. The maximum delta between your initial max_memory setting and the true total buffer size occurs right after you break through a 64K boundary, for example if you set max_memory = 576 KB (see the green line in the table), your actual buffer size will be closer to 767 KB in a non-partitioned event session. You get “stepped up” for every 191 KB block of initial max_memory which isn’t likely to cause a problem for most machines.

Things get more interesting when you consider a partitioned event session on a computer that has a large number of logical CPUs or NUMA nodes. Since each buffer gets “stepped up” when you break a boundary, the delta can get much larger because it’s multiplied by the number of buffers. For example, a machine with 64 logical CPUs will have 160 buffers using per_cpu partitioning or if you have 8 NUMA nodes configured on that machine you would have 24 buffers when using per_node. If you’ve just broken through a 64K boundary and get “stepped up” to the next buffer size you’ll end up with total buffer size approximately 10240 KB and 1536 KB respectively (64K * # of buffers) larger than max_memory value you might think you’re getting. Using per_cpu partitioning on large machine has the most impact because of the large number of buffers created. If the amount of memory being used by your system within these ranges is important to you then this is something worth paying attention to and considering when you configure your event sessions.

The DMV dm_xe_sessions is the tool to use to identify the exact buffer size for your sessions. In addition to the regular buffers (read: event session buffers) you’ll also see the details for large buffers if you have configured MAX_EVENT_SIZE. The “buffer steps” for any given hardware configuration should be static within each partition mode so if you want to have a handy reference available when you configure your event sessions you can use the following code to generate a range table similar to the one above that is applicable for your specific machine and chosen partition mode.

DECLARE @buf_size_output table (input_memory_kb bigint, total_regular_buffers bigint, regular_buffer_size bigint, total_buffer_size bigint)
DECLARE @buf_size int, @part_mode varchar(8)
SET @buf_size = 1 -- Set to the begining of your max_memory range (KB)
SET @part_mode = 'per_cpu' -- Set to the partition mode for the table you want to generate

WHILE @buf_size <= 4096 -- Set to the end of your max_memory range (KB)
BEGIN
BEGIN TRY

        IF EXISTS (SELECT * from sys.server_event_sessions WHERE name = 'buffer_size_test')
DROP EVENT SESSION buffer_size_test ON SERVER
DECLARE @session nvarchar(max)
SET @session = 'create event session buffer_size_test on server
add event sql_statement_completed
add target ring_buffer
with (max_memory = ' + CAST(@buf_size as nvarchar(4)) + ' KB, memory_partition_mode = ' + @part_mode + ')'

        EXEC sp_executesql @session

        SET @session = 'alter event session buffer_size_test on server
state = start'

        EXEC sp_executesql @session

        INSERT @buf_size_output (input_memory_kb, total_regular_buffers, regular_buffer_size, total_buffer_size)
SELECT @buf_size, total_regular_buffers, regular_buffer_size, total_buffer_size FROM sys.dm_xe_sessions WHERE name = 'buffer_size_test'
END TRY
BEGIN CATCH
INSERT @buf_size_output (input_memory_kb)
SELECT @buf_size
END CATCH
SET @buf_size = @buf_size + 1
END

DROP EVENT SESSION buffer_size_test ON SERVER

SELECT MIN(input_memory_kb) start_memory_range_kb, MAX(input_memory_kb) end_memory_range_kb, total_regular_buffers, regular_buffer_size, total_buffer_size from @buf_size_output group by total_regular_buffers, regular_buffer_size, total_buffer_size

Thanks to Jonathan for an interesting question and a chance to explore some of the details of Extended Event internals.

- Mike