Enabling Memory Support for Over 4 GB of Physical Memory

 
 

To enable Address Windowing Extensions (AWE) for Microsoft SQL Server, you must run the SQL Server 2005 Database Engine under a Microsoft Windows account that has been assigned the Lock Pages in Memory option and use sp_configure to set the awe enabled option to 1. The Maximize data throughput for network application option of Network Connection in Control Panel must be selected.

 
 

To enable AWE for an instance of SQL Server 2005, use sp_configure to set the awe enabled option to 1, and then restart SQL Server. Because AWE is enabled during SQL Server startup and lasts until SQL Server shutdown, SQL Server will notify users when awe enabled is in use by sending an “Address Windowing Extensions enabled” message to the SQL Server error log. For more information about the awe enabled configuration option, see awe enabled Option.

 
 

Enable the Lock Pages in Memory Option

 
 

The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). 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. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Although not required, we recommend locking pages in memory when using 64-bit operating systems.

 
 

Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server 2005 Database Engine. You must be a system administrator to change this policy.

 
 

For a table that lists the maximum server memory values, see Memory Architecture.

 
 

To enable the lock pages in memory option

 
 

1 – On the Start menu, click Run. In the Open box, type gpedit.msc.

The Group Policy dialog box opens.

 
 

2 – On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

 
 

3 – Expand Security Settings, and then expand Local Policies.

 
 

4 – Select the User Rights Assignment folder.

The policies will be displayed in the details pane.

 
 

5 – In the pane, double-click Lock pages in memory.

 
 

6 – In the Local Security Policy Setting dialog box, click Add.

 
 

7 – In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

 
 

 
 

Microsoft SQL Server 2005 uses the Microsoft Windows Address Windowing Extensions (AWE) API to support very large amounts of physical memory. SQL Server 2005 can access up to 64 gigabytes (GB) of memory on Microsoft Windows 2000 Server and Microsoft Windows Server 2003.

 
 

Note:

Support for AWE is available only in the SQL Server 2005 Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems. SQL Server 2005 Analysis Services (SSAS) cannot take advantage of AWE mapped memory. If the available physical memory is less than the user mode virtual address space, AWE cannot be enabled.

 
 

Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit processes are therefore limited to 4 GB. By default, on 32-bit Microsoft Windows operating systems, 2 GB are reserved for the operating system, and 2 GB are made available to the application. If you specify a /3gb parameter in the Boot.ini file of Windows 2000 Advanced Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB. For more information about the /3gb parameter, see Process Address Space.

 
 

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 nonpaged memory to the 32-bit address space. Although the 32-bit address space is limited to 4 GB, the nonpaged 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, consider the following:

 
 

AWE allows allocating physical memory over 4 GB on 32-bit architecture. AWE should be used only when available physical memory is greater than user-mode virtual address space.

 
 

 
 

To support more than 4 GB of physical memory on 32-bit operating systems, you must add the /pae parameter to the Boot.ini file and reboot the computer. For more information, see your Windows documentation.

 
 

Note:

In Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices. In this case, you do not have to use the /pae parameter on a system that is configured to use hot-add memory devices. In all other cases, you must use the /pae parameter in the Boot.ini file to take advantage of memory over 4 GB.

 
 

 
 

If there is more than 16 GB of physical memory available on a computer, the operating system requires 2 GB of virtual address space for system purposes and therefore can support only a 2 GB user mode virtual address space. For the operating system to use the memory range above 16 GB, be sure that the /3gb parameter is not in the Boot.ini file. If it is, the operating system cannot use any physical memory above 16 GB.

 
 

 
 

Note:

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, threads, databases, queries, and other objects that permanently reside in the virtual address space.