SQL Server Memory: Any size page allocator replaces single and multi-page allocator

Since SQL Server 2012 single and multi-page allocators are replaced by the “any size” page allocator. The Max Server Memory configurable now includes multi-page allocations .  It’s important news as a  SQL Server DBA when sizing the  max server memory value.

Since SQL Server 2012 multi-page and CLR allocations are included within the  max server memory(MB) and min server memory (MB)  limits.  Before SQL Server 2012 , max server memory(MB) and min server memory (MB)  SQL Server configurables did not include Multi-page allocations and CLR allocations.

Before SQL Server 2012 , there were two memory managers . The single allocator managed allocations 8k or less.  The multi-page allocator managed allocations greater than 8k.

I still read inaccurate advice  in forums , example “Just to make things more clear SQL Server 2012 can take memory beyond value given in max server memory. This is because memory for large page request is not satisfied by buffer pool and is done by windows API directly which again comes under memory consumed by SQL Server…” This statement was accurate previous to SQL Server 2012 , but since SQL Server 2012 is inaccurate.

The image describes the changes. There are still some Direct OS reservation , but the bulk of the memory work is controlled by the max server memory

Image reference: Memory Manager surface area changes in SQL Server 2012

Read More

SQL Server – Memory: Pages sec, memory pressure and thrashing

Sys.dm_os_memory_clerks and AWE memory allocation

SQL Server – Windows Virtual memory and paging file location