I want to touch upon a very common configuration problem that I encounter with a surprisingly large number of my clients—failure to correctly configure SQL Server to be able to take advantage of more than 2GB of physical memory. For more information on SQL Server, see "Free SQL Server Resources for Developers."
SQL Server and AWE Memory
To overcome this problem, Microsoft created AWE, an improved API that lets applications address a larger pool of memory. The trick though, is that this memory isn't managed like normal, “dynamic,” memory and requires applications that wish to take advantage of it to make use of special API calls and access permissions. SQL Server, of course, was built to efficiently take advantage of Microsoft's AWE APIs, but it's that last little bit (the special privileges) that causes so much grief.
The Real Source of the Problem
Given the large number of deployments I've encountered where AWE hasn't been correctly configured, I'm personally convinced that the root of this problem doesn't lie with the folks installing SQL Server. Instead, I fault Microsoft because they really should make this aspect of configuring SQL Server easier. For example, when you install SQL Server, you need to specify a user account that the SQL Server Service will use. You can use a local account, a domain account (either of these should be restricted using the principle of least privilege), or you can use the LOCAL SYSTEM or NETWORK SERVICE accounts. But if you chose an account that doesn't have the LOG ON AS SERVICE system privilege already enabled, the SQL Server installer will go ahead and make this change for you. Otherwise, your SQL Server wouldn't be able to start the next time you rebooted your server.
Strangely enough though, what's required to allow applications to use AWE memory is another system privilege: LOCK PAGES IN MEMORY. Without this ability, your SQL Server service account can't interact with the AWE APIs as it doesn't have the correct security permissions. But, for some reason, the SQL Server installation process doesn't make this change for you. Instead - you need to go in and manually add this permission by modifying your system's local security policy. And it's this exact problem that causes me to fault Microsoft for why so many SQL Server deployments aren't correctly configured. Accordingly, if I ran the universe, Service Packs for SQL Server would include a new option during installation that would let administrators opt OUT of granting their selected service accounts the LOCK PAGES IN MEMORY privilege.
AWE Warning Signs Happily, if you're using a 64-bit operating system, then your applications (like SQL Server) will automatically be able to address more than 2GB of RAM and you're fine. Likewise, if all of your databases (on a given server) are much smaller than 2GB in size, then you probably don't need to worry about AWE memory and SQL Server. Likewise, if your server doesn't have more than 2GB of memory currently installed - then you won't really need to worry about this either (unless you need to use or add more RAM later on).
Interestingly enough, one of the quickest and easiest ways to tell if there is a problem is by simply looking at the sizes of the databases on the server (are there more than 2GB of data that could or should be in memory?), and then checking the Performance Tab in Windows Task Manager to see if more than 2GB of RAM is being used. If it looks like more than 2 GB should be used and only 2GB (or something like 2.07 GB) is being used, I can usually assume that AWE hasn't been correctly configured.
Correcting the Problem
If you have more than 2GB of RAM available and your databases could use the extra memory, setting up AWE is pretty simple. First, you'll want to make sure that your application has the correct permissions necessary to use AWE memory and supporting APIs. To do this, you'll need to modify your system's Local Security Policy. Depending upon which version of Windows Server you're using (i.e. 2000, 2003, or 2008) you should be able to find the Local Security Policy under Start, Administrative Tools, Local Security Policy. From there, you'll want to navigate into to the Security Settings, Local Policies, User Rights Assignment node, and then find the Lock pages in memory policy/privilege. Here you'll add the name of the service account your SQL Server is currently running under (which you can get by running services.msc and looking at the MSSQL service(s) for any/all instances you want to configure) and then close the dialog box.
Then, after you've taken care of permissions, you'll want to ensure that SQL Server itself has been configured to take advantage of AWE Memory. Doing so is pretty easy, and can be handled with the following script:
-- Checking for AWE:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
-- A config_value/run_value of 0 means that AWE is not enabled
EXEC sp_configure 'awe enabled'
GO
-- Enabling AWE:
-- (Make sure to grant 'lock pages in memory' to
-- SQLService account and restart SQL after this change)
EXEC sp_configure 'awe enabled', 1
RECONFIGURE
GO
Depending upon what other kinds of applications are running on your system (or what kind of load your server is typically under) you may also want to look at modifying the min server memory and max server memory configuration options as well. (To learn more, or troubleshoot this setup process, check out Microsoft KB article 274750).
Otherwise, after ensuring both of these configuration options, you'll need to restart your SQL Server Service (if you made any changes) and as it restarts, your SQL Server service will note that it has the necessary permissions to use AWE and will then begin using additional RAM as necessary.
Depending upon what other kinds of applications are running on your system (or what kind of load your server is typically under) you may also want to look at modifying the min server memory and max server memory configuration options as well. (To learn more, or troubleshoot this setup process, check out Microsoft KB article 274750).
