SQL Server 2005 and AWE

AWE? What is that?

If you are like me, you may have often wondered about the purpose of that check box in SQL Server. I know I did. Well, my friend Jayson Rowe and I read up on it and actually implemented AWE on one of our SQL Servers at work.

On a default 32 bit Windows Server setup, 2 GB of RAM is the most that a SQL instance can use. Let’s say a server has 10 GB of RAM that is running nothing but the OS and SQL. The operating system will allocate 2 GB to the OS and 2 GB for SQL. That means your server is wasting 6 GB of RAM.

There is a optional parameter (/PAE)that you can place in the boot.ini file to change that.

To edit the boot.ini file on a Windows 2003 server, open Control Panel, open System, then click Advanced, then settings for the Startup and Recovery, then Edit under System startup. Then you will see a file whose contents are similar to the following:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="WindowsServer 2003, Standard" /fastdetect /NoExecute=OptOut

In order to enable the PAE switch, simply add /PAE to the end of the OS loader line, save, and reboot.

What is PAE? It is Physical Address Extension.

So… What is that? Here is a quote straight from this Technet article.

PAE changes the memory addressing from 32-bit addressing mode to 64-bit addressing mode, allowing the operating system and high performance drivers and applications access to the additional physical memory

So, we have discussed OS level setting of PAE.

After setting the PAE, a reboot of the server is required.

After the reboot, open SQL Server Management Studio, right click on the server instance and select properties. There is a check box to enable AWE after PAE is turned on (see picture in this post).

Restart the SQL Server instance.

But there is one more important piece of information to consider… We learned the hard way.

There is a setting in the registry called "Lock Pages in Memory." We first turned on PAE and AWE… and noticed the server doing some insane page swapping on the hard disk. We found after a little research that we needed to change the "Lock Pages in Memory" registry setting to not use the swap file but use physical memory. Here are the steps straight from the Technet article.

1. On the Start menu, click Run. In the Open box, type gpedit.msc.
   The Group Policy dialog box opens.
2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.
   The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Policy Setting dialog box, click Add.
7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

WARNING: Quote from this AWESOME article (I encourage you to check it out… Jim knows more on this subject than I do… that’s for sure!)

It is always recommended to set a value for "Max Server Memory" to ensure that other processes running on the server have some memory available.  Leaving 1.5-2 GB is a good setting.  Additionally, when multiple instances of SQL Server are running on the one machine, "Max Server Memory" is necessary to prevent one instance from using too much memory and starving the other.

In recap… Here are the three steps needed to be able to use AWE on your SQL Server.

1. Add the /PAE switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory
2. Grant "Lock Pages in Memory" to the user account that runs the SQL Server process
3. Check the checkbox in the SQL Instance properties to allow AWE.

 

So… Back to our server.

Our server was only using 4 of the 8 GB of RAM on the server. After turning on PAE and AWE… SQL is now using 7 GB of RAM and the server is running extremely well.

I am not the expert, so if you can add anything to this post, please feel free to leave a comment.

Special thanks again to Jim McLeod and Jayson Rowe.