Sunday, December 29, 2024
Latest:

SQLChamp

Let's learn together!

LearnSQL

Memory Configuration Changes | SQL Server 2012

Sarabpreet Singh Anand

Hi Friends,

There are a few changes in SQL Server 2012 in Memory Manger, and a DBA should be aware of such changes before upgrading or installation of SQL 2012 Instance on production.

In Earlier versions of SQL Server (before SQL 2012) SQL Server was also consuming memory outside of the limit defined in Max Server Memory. Those who don’t know, in earlier versions SQL Server was allocating memory mainly in two ways Single Page and Multi Page. Single page allocation was consuming memory from the limit defined in Min & Max Memory Setting; the main consumer of this type of memory is Buffer Pool. Multi-Page allocation was capable to consume memory out of the limit which is S0_Called available for OS.

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 & min server memory (MB)”

In earlier versions of SQL Server (2005, 2008, 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).

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 allocation SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 SQL Server 2012
Single-page allocations Yes Yes, consolidated into “any size” page allocations
Multi-page allocations No Yes, consolidated into “any size” page allocations
CLR allocations No Yes
Thread stacks memory No No
Direct allocations from Windows No No

 

Changes to “memory_to_reserve”

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.

AWE Feature is not available in SQL Server 2012

http://www.sarabpreet.com/2016/02/sql-server-awe-enabled-option-deprecated/1509

Even though the “awe enabled” feature is not available in 32-bit SQL Server 2012, you can still use the “locked pages” feature by assigning the “lock pages in memory” user right for the SQL Server startup account.

Carefully review the memory requirements of the existing instance of SQL Server before you upgrade to SQL Server 2012.

Do let us know your thoughts on this by leaving a comment.

Regards

Sarabpreet Anand