You are currently offline, waiting for your internet to reconnect

Memory configuration and sizing considerations in SQL Server 2012 and later versions

Summary
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:
  • sp_configure options (max server memory (MB), min server memory (MB), and awe enabled)
  • -g startup parameter (memory_to_reserve)
  • locked pages

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.

Note This article also applies to Microsoft SQL Server 2014.
More information
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's no separate "Multi-Page allocations" category 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:
  • max server memory (MB)
  • min server memory (MB)
This configuration option typically included only memory allocations that were less than or equal to 8 KB in the SQL Server process. These allocations were also referred to as "single_page_allocations" because the SQL Server memory manager used a page size of 8 KB. This configuration did not include the following memory allocation requests:
  • Multi-Page allocations from SQL Server: These are allocations that request more than 8 KB.
  • CLR allocations: These allocations include the SQL CLR heaps and its global allocations that are created during CLR initialization.
  • Memory allocations for thread stacks in the SQL Server process.
  • Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL Server process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

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.
Type of memory allocationSQL Server 2005, SQL Server 2008, SQL, Server 2008 R2SQL Server 2012, SQL Server 2014
Single-page allocationsYesYes, consolidated into "any size" page allocations
Multi-page allocationsNoYes, consolidated into "any size" page allocations
CLR allocationsNoYes
Thread stacks memoryNoNo
Direct allocations from WindowsNoNo
SQL Server 2012 and later versions might allocate more memory than the value that's specified in the max server memory setting. This behavior may occur when the Total Server Memory (KB) value has already reached the Target Server Memory (KB) setting (as specified by max server memory). If there is insufficient contiguous free memory to meet the demand of multi-page memory requests (more than 8 KB) because of memory fragmentation, SQL Server can perform over-commitment instead of rejecting the memory request.

As soon as this allocation is performed, Resource Monitor starts to release the allocated memory and tries to bring the Total Server Memory (KB) value below the Target Server Memory (KB)  specification. Therefore, SQL Server memory usage could briefly exceed the max server memory setting. In this situation, the Total Server Memory (KB) performance counter reading will exceed the max server memory and Target Server Memory (KB) settings.

This behavior is typically observed during the following operations: 
  • Large Columnstore index queries
  • Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations
  • Backup operations that require large memory buffers
  • Tracing operations that have to store large input parameters

Cumulative Update 8 for SQL Server 2014 and Cumulative Update 1 for SQL Server 2014 SP1 introduced an improvement for faster memory release, as described in the preceding overview. Also, Cumulative Update 9 for SQL Server 2014 address a memory allocation issue that involves over-commitment. For more information, see the following Microsoft Knowledge Base articles:
For more information about the portions of memory that are controlled by the max server memory setting, see the Server Memory Server Configuration Options Books Online topic.

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:
  • Multi-Page allocations from SQL Server: These are allocations that request more than 8KB.
  • CLR allocations: These allocations include the SQL CLR heaps and its global allocations that are created during CLR initialization.
  • Memory allocations for thread stacks in the SQL Server process.
  • Memory allocation requests made directly to Windows: These allocation requests include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL Server process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

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.
Type of memory allocationSQL Server 2005, SQL Server 2008, SQL Server 2008 R2SQL Server 2012, SQL Server 2014
Single-page allocationsNoNo, consolidated into "any size" page allocations
Multi-page allocationsYesNo, consolidated into "any size" page allocations
CLR allocationsYesYes
Thread stacks memoryYesYes
Direct allocations from WindowsYesYes

Changes to other memory-related configuration options

If 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:

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:

Maximum memory utilization for various editions of SQL Server

Different editions of SQL Server have maximum limits on various resources they can use. There are limits enforced on maximum memory usage for different editions of SQL Server. For example, a SQL Server 2008 R2 Standard Edition instance can use only a maximum memory of 64 GB. For complete details, go to the following MSDN webpage:Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). The rest of the caches in the SQL Server memory manager can use much more memory than is specified by these edition limits. For example, a SQL Server 2012 Express edition can use only a maximum size of 1.4 GB for its database cache. Other caches (such as the procedure cache, the metadata cache, and so on) can consume memory up to the size specified by the "max server memory" configuration.

Properties

Article ID: 2663912 - Last Review: 11/06/2015 21:14:00 - Revision: 7.0

Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2014 Enterprise

  • kbtshoot KB2663912
Feedback
>