Memory Manager Configuration changes in SQL Server 2012
Continuing from yesterday's article about Memory Manager surface area changes in SQL Server 2012, this post looks at corresponding configuraton changes for the new Memory Manager. The surface area change article mentioned that the Memory Manager redesign resulted in being able to more accurately size and govern all SQL Server memory consumption. Let's look at what this means in terms of sizing the total memory usage.
Max server memory
In SQL Server 2008, the max server memory configuration setting only governed single 8K page allocations. CLR allocations, multi-page allocations, direct Windows allocations (DWA) and memory required by thread stacks would not be included, so you'd have to set the -g memory_to_reserve startup setting on 32-bit systems if you're using CLR for example, to reserve sufficient address space.
Setting max server memory becomes more straightforward with SQL Server 2012. The redesigned Memory Manager takes a central role in providing page allocations for the other components, and the max server memory setting governs all memory manager allocations.
With all page allocations are governed, including CLR page allocations, instance sizing is more predictable, which can help a lot in memory constrained and multi-instance scenarios. Direct Windows Allocations, (i.e. calls to VirtualAlloc()) will still remain outside of Memory Manager control.
Here's a summary of the how the memory startup option settings changed in 2012:
SQL Server 2008 R2
Sp_configure option |
Default setting |
Lowest possible value |
Highest possible value |
Min server memory |
0 |
16 (MB) |
Value less than max server memory setting |
Max server memory |
2147483647 (available memory in the system) |
16 (MB) |
2147483647 (available memory in the system)
|
SQL Server 2012
Sp_configure option |
Default setting |
Lowest possible value |
Highest possible value |
Min server memory |
0 |
16 (MB) |
Value less than max server memory setting |
Max server memory |
2147483647 (available memory in the system) |
32 bit - 64 (MB) 64 bit – 128 (MB) |
2147483647 (available memory in the system) |
sp_configure awe_enabled
SQL Server 2008 R2 was the last release to support the awe_enabled option, which allowed use of memory above 4GB on 32-bit systems. In SQL Server 2012 32-bit architectures can no longer use >4GB, though you can still set this value on 32-bit systems to allow the use of locked pages but it's not needed on 64-bit. Note this setting does not affect the way Address Windowing Extensions are use to implement locked pages in memory on 64-bit systems.
- Guy