Using Address Windowing Extensions (AWE) with SQL Server

I discovered the following while researching why a recently installed instance of SQL Server wouldn’t use any more memory than 2Gb, even though the ‘Use AWE to allocate memory’ flag was set.

Using AWE with SQL Server

In order to allow SQL Server to use all of the memory available to the operating system, the Windows ‘Address Windowing Extensions’ (AWE) facility must be used by enabling the ‘Use AWE to allocate memory’ flag in the SQL Server Properties dialog, or alternatively by issuing the following command in a query against the target server:

sp_configure 'awe enabled', 1
RECONFIGURE
GO

The change requires a restart to SQL Server, however before you do that, ensure that you add the user that the SQL Server service is running under to the ‘Lock Pages in Memory’ Local Security Policy (see Microsoft KB 811891 for the exact details on how to do this). If you don’t update the local security policy, SQL Server will not actually use AWE and continue to use only 2Gb of memory; furthermore, you’re likely to see the following in the SQL Server log:

Cannot use Address Windowing Extensions because lock memory privilege was not granted.

A Small Caveat

I mentioned above that the AWE flag allows SQL Server to use all of the memory available to the operating system. This isn’t exactly true, as it depends on the actual physical memory available *and* how much Windows can actually ‘see’.

In researching this problem, I spent some time digging into the memory management model of Windows and found some extremely interesting information relating to the /3GB, /PAE etc. boot.ini switches. This research was a real learning curve and information I think anyone putting together an enterprise infrastructure should be aware of – I plan on blogging the various options over the next few days.

Advertisements

One thought on “Using Address Windowing Extensions (AWE) with SQL Server

  1. Hi,

    Interesting post!

    And yes, the /3GB, /PAE, AWE are the necessary evils that many people are still using, the company I work for included.
    If you intend to write a post about it, you might also want to include a paragraph on SQL Server 2005 64 bit, there is a very nice and short whitepaper about advantages of SQL Server 64 bit at http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx

    I presonnally believe that for new server set up, there is no reason to not go for the 64 bits road, except would there be a compelling reason to not do so (i.e. some old app or driver for some sub system)

    Anyway it’s just my 2 cents… or should I say pences? ;)

    Francois Malgreve.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s