Optimize SQL Server 2000, 2005 or 2008 in Large RAM System by Locking Pages in Memory and AWE

Optimize SQL Server 2000, 2005 or 2008 in Large RAM System by Locking Pages in Memory and AWEMicrosoft SQL Server is a data-intensive and disk I/O (read and write) intensive database management systems. For SQL Server which is running on a system with large amount or big size of physical RAM memory, such as system with 4GB, 8GB or even more gigabytes of physical memory, it 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 access.

With 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. 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 run SQL Server, as AWE memory cannot be swapped out to the page files. Note that AWE is not required for 64-bit system, but Lock Pages in Memory privilege is recommended for 64-bit system though.

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 are 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. And, the awe enabled option 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.