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

Microsoft 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.

Related Articles

• How to Set Compatibility Level for SQL Server 2008 or 2005 Database
• Microsoft DreamSpack Offers Free Download and License of Visual Studio 2008, Server 2003, SQL Server 2005 and More
• How to Remotely Enable Remote Desktop (Terminal Services or RDP) via Registry in Windows 2000/XP/2000/Vista/2008
• Uninstall and Remove Multiple Database Instances of Microsoft SQL Server 2005
• Microsoft Virtual Server 2005 R2 SP1 (Enterprise Edition) Direct Download Links
• Microsoft Virtual Server 2005 R2 Download Available for Free
• Error Has Occurred While Establishing A Connection To SQL Server 2005 Which Does Not Allow Local and Remote Connections
• Stay Updated with Web Pages Changes via Web Pages Monitor
• Internet Explorer (IE) Hang When Opening Virtual Server 2005 Web Administration Site
• Free Ramdisk for Windows Vista, XP, 2000 and 2003 Server

This entry was posted on Saturday, June 7th, 2008 at 1:34 am and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.