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