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