Optimize SQL Server 2005/8 in Large RAM Systems with AWE

Microsoft SQL Server is a data-intensive and disk I/O (read and write) intensive database management system. SQL Server running on systems with a large amount of physical RAM memory (8 GB or more) can be configured to use the Address Windowing Extensions (AWE) API to provide access to physical memory in excess of the limits set on configured virtual memory, and force all paging to take place in memory for faster data and thread access.

With the Address Windowing Extensions (AWE) API, Microsoft SQL Server can support and access very large amounts of physical memory, upwards of 64 gigabytes or more on Windows 2000 Server, Windows Server 2003 and Windows Server 2008 (R2). The specific amount of memory SQL Server can use depends on hardware configuration and operating system support.

Before enabling AWE, Lock Pages in Memory permission must be granted to the user account that runs SQL Server as AWE memory cannot be swapped out to the page file. Note that AWE is not required for 64-bit systems, but the Lock Pages in Memory privilege is recommended for 64-bit systems.

Step 1: Enable PAE support on Windows Server to allow large segment of physical memory to be used.

Step 2: Assign to enable Lock Pages in Memory permission to SQL Server account.

Step 3: Enable AWE Option

Note that in Windows 2000 (Windows Server 2003 and 2008 provide dynamic allocation on demand), if a value for max server memory is not specified, SQL Server reserves almost all available memory during startup, leaving 128 megabytes (MB) or less physical memory for other applications. Enabling AWE mode is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change AWE enabled only when show advanced options is set to 1.

To enable AWE and configure the min server memory to 1 GB (so that AWE mapped memory can be released up until 1 GB) and the max server memory to 6 GB, use the following commands:

sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
sp_configure ‘awe enabled’, 1
RECONFIGURE
GO

Restart SQL Server with the following commands:

net stop mssqlserver
net start mssqlserver

Then, configure memory:

sp_configure ‘min server memory’, 1024
RECONFIGURE
GO
sp_configure ‘max server memory’, 6144
RECONFIGURE
GO

Restart the SQL Server after all configuration to make the changes effective. To disable AWE, simply set the awe enabled to 0 and execute the RECONFIGURE statement again.