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

Comments

• Anonymous
  October 21, 2012
  Cloud servers are worth to deploy anything online with its reliable and secure features.

• Anonymous
  October 28, 2012
  Hi, Bowerman In this post, you said "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. "   Does the "set this value" means to enable AWE? But, there is no 'AWE enabled' option in the memory page of SQL Server instance properties, and no entry in sys.sysconfigures, how to enable it? My SQL Server version is 11.0.2100.60 (Intel X86) ,EE. Thanks!

• Anonymous
  October 29, 2012
  Hey Kurt, Sorry for the confusion. The "AWE enabled" option is deprecated in SQL Server 2012, and no longer needed to set locked pages. See support.microsoft.com/.../2644592 for clarification, and also refer to "How to enable locked pages in SQL Server 2012": support.microsoft.com/.../2659143.. "Starting with SQL Server 2012, the memory manager simplifies the usage of "locked pages" across supported editions and processor architectures. In SQL Server 2012, you can enable "locked pages" by granting the "lock pages in memory" user right for the startup account for the instance of SQL Server in Windows. This is applicable for Standard, Business Intelligence, and Enterprise editions of SQL Server 2012 running on both 32-bit and 64-bit processor architectures.". Regards Guy

• Anonymous
  February 19, 2014
  Hi Guy, The KB article support.microsoft.com/.../2663912 mentions that the Direct Windows Allocations and Thread Stacks allocations are not controlled by the max server memory setting in SQL 2012. However, the image above shows that the max server memory includes DWA and TS. Could you please clarify on this? Thanks, Harsh

• Anonymous
  February 19, 2014
  The comment has been removed

• Anonymous
  July 09, 2014
  Ty for the blog, Guy. After also reading this support.microsoft.com/.../2663912, I still try to find how CLR allocations are controlled by min/max memory settings while still being part of memory_to_reserve. Ty for any tip on where to look for more info.