Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Memory configuration and sizing considerations in SQL Server 2012
Article ID: 2663912 - View products that this article applies to.
Microsoft SQL Server performs automatic and dynamic memory management based on the current memory requirements of the internal SQL Server components and workload on the system. SQL Server also offers memory-related configuration options to aid fine tuning for specific application behavior patterns and specific advanced requirements. The different configuration options include the following:
Microsoft SQL Server 2012 introduces changes in memory manager that affect how administrators configure these memory options. Please review the changes that are described here before you upgrade to SQL Server 2012 or before you configure a new installation of SQL Server 2012.
SQL Server 2012 also introduces changes in the way specific types of memory allocations are accounted and the memory configuration options that control the maximum amount of memory that can be used by these types of allocations. The changes in SQL Server 2012 are specific to memory allocation requests from SQL CLR and from Multi-Page allocations. SQL Server 2012 has a new page allocator that manages both single-page and multi-page allocations (less than 8 KB and greater than 8 KB allocation requests). Therefore, there is no separate categorization that is called "Multi-Page allocations" in SQL Server 2012.
Changes to "max server memory (MB)" and "min server memory (MB)"In earlier versions of SQL Server (SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2), the following configuration options determined the limits of physical memory that the buffer pool consumed. Notice that we are talking about physical memory in this case, physical memory that is committed by the SQL Server database engine process:
Starting with SQL Server 2012, Multi-Page allocations and CLR allocations are also included in memory limits that are controlled by max server memory (MB) and min server memory (MB). This change provides a more accurate sizing ability for all memory requirements that go through the SQL Server memory manager. Carefully review your current max server memory (MB) and min server memory (MB) values after you upgrade to SQL Server 2012. You should review such values because SQL Server 2012 now includes and accounts for more memory allocations compared to earlier versions. These changes apply to both 32-bit and 64-bit versions of SQL Server 2012.
The following table indicates whether a specific type of memory allocation is controlled by the max server memory (MB) and min server memory (MB) configuration options.
Collapse this tableExpand this table
Changes to "memory_to_reserve"
In earlier versions of 32-bit SQL Server (SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2), the SQL Server memory manager set aside a part of the process virtual address space for use by the following allocation requests:
The virtual address space that is reserved for these allocations is determined by the memory_to_reserve configuration option. The default value that SQL Server uses is 256 MB. To override the default value, use the SQL Server -g startup parameter. This part of the virtual address space is also known as "Memory-To-Leave" or "non-Buffer Pool region."
Because SQL Server 2012 has the new "any size" page allocator that handles allocations greater than 8 KB, the memory_to_reserve value does not include the multi-page allocations. Except for this change, everything else remains the same with this configuration option.
The following table indicates whether a specific type of memory allocation falls into the memory_to_reserve region of virtual address space for the SQL Server process.
Collapse this tableExpand this table
Changes to other memory-related configuration optionsIf you configured SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 to use the "awe enabled" option, please review the following article to understand important changes for this configuration option:
The "awe enabled" SQL Server feature is deprecated
If you configured SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 to use the "locked pages" feature, please review the following article to understand important changes for this configuration option:
How to enable "locked pages" feature in SQL Server 2012
SQL Server Memory Manager Changes in Denali
New SQLOS features in SQL Server 2012