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.


3 Responses to “Optimize SQL Server 2000, 2005 or 2008 in Large RAM System by Locking Pages in Memory and AWE”

  1. ppamo
    September 6th, 2008 03:19
    3

    nice article, but I get in here finding spanish resources, and get into the automatically translated version to spanish, nice the functionality for your blog, but the machine’s spanish is unreadable.

    again thanks, for the article.

  2. torrentleech
    August 7th, 2008 11:10
    2

    can we get working crack/key for
    Microsoft SQL Server 2008
    thanks

  3. Enable Lock Pages in Memory to Prevent Database Paging to Disk » Tip and Trick
    June 7th, 2008 01:37
    1

    [...] RAM is definitely faster than disk I/O. Lock Pages in Memory option privilege must be enabled to configure Address Windowing Extensions (AWE) which allow a 32-bit software application to access more physical memory than it has virtual [...]

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe to comments feature has been disabled. To receive notification of latest comments posted, subscribe to My Digital Life Comments RSS feed or register to receive new comments in daily email digest.
Custom Search

New Articles

Incoming Search Terms for the Article

sql server memory - sql 2005 awe - sql 2008 ram - Cannot use Large Page Extensions: lock memory privilege was not granted. - sql 2008 memory - optimize SQL Server 2008 - enableing awe - SQL Server RAM - awe sql 2005 - Address Windowing Extensions (AWE) - force sql server 2008 to use more ram - AWE sql server 2008 - SQL Server 2008 RAM - Physikal Address Extension Address Windowsing Extension SQL - increasing sql 2005 memory - sql server physical memory - Please restart SQL server in Address Windowing Extensions (AWE) mo - configure sql 2005 memory - AWE and SQL Server 2008 64 bit - optimizar sql server 2005 - SQL 2008 max server memory - sql server 2008 x64 memory - optimizing sql 2005 - sql 2008 memory recommendations - sql 2005 configuration awe 8b - sql 2005 memory optimization - sql server database in ram - awe sql server 2005 virtual server - max server memory sql 2008 - ram needer for sql 2008 - sql ram - windows 2008 large page memory - how to enable sql awe api - SQL Server Large Page Extensions - how to set ram usage in sql server 64 bit - SQL SERVER 2000 8GB RAM AWE WIN 2003 SERVER - sql 2005 awe configuration - awe sql server memory - optimize windows 2003 for sql server - sql 2008 awe - sql 2005 memory usage awe - SQL 2005 Configure to change max amount memory - SQL 2008 memory usage - "Cannot use Large Page Extensions" 32mb - Address Windowing Extensions - cannot use large memory pages - how do i configure sql server 2005 to max memory - large page extensions enabled - optimize sql server - optimizing sql server 2008 -