3 Mar

Memory Configuration Changes | SQL Server 2012

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


18 Feb

Memory Shortage for all 32 Bit SQL Server 2012 Instances

Hi Friends,

As you all know AWE has been deprecated and not available from SQL Server 2012 (Find details here), as a result when you upgrade from SQL Server 2008 R2 to the next version of SQL Server,you cannot use more memory than what the virtual address space limits in 32-bit instances of SQL Server. If you must have more memory for this instance of SQL Server, then you have to migrate to a 64-bit instance of SQL Server.

After you upgrade to SQL Server 2012, the maximum amount of memory that can be used by the 32-bit instance of SQL Server is determined as follows.

SQL Server and operating system settings Maximum amount of memory used by SQL Server
32-bit SQL Server on 32-bit OS

2 GB

32-bit SQL Server on 32-bit OS with /3G boot option 3 GB
32-bit SQL Server on 64-bit OS 4 GB

Those who don’t know much about AWE;

AWE was being used in earlier versions of 32 Bit SQL Servers so that they can support more than 4GB of Physical Memory. SQL Server can access up to 64 GB of memory on Microsoft Windows Server 2000 & 2003.

By Default, Standard 32-bit addresses can map a maximum of 4 GB of memory. By default, on 32-bit Microsoft Windows operating systems, 2 GB is reserved for the operating system, and 2 GB is made available to the application. If you specify a /3gb parameter in the Boot.ini file of Windows Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB.

AWE is a set of extensions to the memory management functions of Windows that allow applications to address more memory than the 2-3 GB that is available through standard 32-bit addressing. AWE lets applications acquire physical memory, and then dynamically map views of the non-paged memory to the 32-bit address space.

Although the 32-bit address space is limited to 4 GB, the non-paged memory can be much larger. This enables memory-intensive applications, such as large database systems, to address more memory than can be supported in a 32-bit address space.

Before you configure the operating system for AWE on 32-bit operating systems, you must add the /pae parameter to the Boot.ini file and reboot the computer. Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices in Windows Server 2003.

The SQL Server buffer pool can fully utilize AWE mapped memory; however, only database pages can be dynamically mapped to and unmapped from SQL Server’s virtual address space and take full advantage of memory allocated through AWE. AWE does not directly help supporting additional users, databases, queries, and other objects that permanently reside in the virtual address space.

Lock pages in Memory

This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. The Lock Pages in Memory option is set to OFF by default in SQL Server. If you have system administrator permissions, you can enable the option manually by using the Windows Group Policy tool (gpedit.msc) and assign this permission to the account that SQL Server is running.

Although it is not required, we recommend locking pages in memory when using 64-bit operating systems. For 32-bit operating systems, Lock pages in memory permission must be granted before AWE is configured for SQL Server.

Regards

Sarabpreet Anand


16 Feb

SQL Server AWE Enabled Option | Deprecated

Like we already know, Windows-based applications can use Windows AWE (Address Windowing Extensions) APIs to allocate and to map physical memory into the process address space. AWE allow 32-bit operating systems to access large amounts of memory. Memory that is allocated by using this method is never paged out by the operating system, provided “Lock Pages In Memory” user right (LPIM) has been granted to the application Service account.

 

Lock Pages in memory is by default given to Local system Service account.

SQL Server supports dynamic allocation of AWE memory on Windows Server. The SQL Server 64-bit version also uses “locked pages” to prevent the process working set (committed memory) from being paged out or trimmed by the operating system. When you enable “locked pages,” it is very important to set an appropriate value for “max server memory” and for “min server memory” configuration options for each instance of SQL Server to avoid system-wide problems.

During startup, AWE reserves only a small portion of AWE-mapped memory. As additional AWE-mapped memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if fewer resources are required, SQL Server can return AWE-mapped memory to the operating system for use by other processes or applications.

AWE Feature is not available in SQL Server 2012.

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.

 

Note:

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, thememory_to_reserve value does not include the multi-page allocations. Except for this change, everything else remains the same with this configuration option.

 

Regards

Sarabpreet Anand