SQL Server AWE

Ran across this problem running SQL Server 2005 Ent edition on Windows Server 2003 64-bit with 8gb memory. Even turning on AWE, it still would only use 3gb memory.

Turns out problem is that the account that SQL Server was running under does not have permissions to lock pages in memory.

1. Run gpedit.msc. On the left hand pane, expand Computer Configuration, expand Windows Settings, expand Security Settings, expand Local Policies, select User Rights Assignment


2. On the right hand pane, find Lock pages in memory and double click, then add your Sql Server startup account into Local Security Policy Setting tab;


3. In Sql Server Management Studio, run:
   

   sp_configure ’show advanced’, 1
   
   reconfigure
   
   sp_configure ‘awe enabled’, 1
   
   reconfigure

   Ignore the error message below, if you have it.
   

   Msg 5845, Level 16, State 1, Line 1
   
   Address Windowing Extensions (AWE) requires
   the ‘lock pages in memory’ privilege which is
   not currently present in the access token of
   the process.



4. Reboot.

Updated 10/26/2009:
While looking up this post for helping an friend with the same problem, I noticed that this now works for Standard edition as well as Enterprise edition. You need Cumulative Update package 2 for SQL Server 2008 Service Pack 1 or Cumulative Update package 4 for SQL Server 2005 Service Pack 3 to provide support for Locked Pages in Standard edition.
http://blogs.msdn.com/psssql/archive/2009/05/19/an-update-for-standard-sku-support-for-locked-pages.aspx