adam hutson

Recently, I had the opportunity to brush up on my SQL Server memory tuning skills.  It's been a while since I needed them, but I thought I'd throw out what I used for someone else's future benefit.

First, some background knowledge:
There are different physical memory limits for each Windows release.
The virtual address space (set of virtual memory addresses) is limited to 4 GB for 32-bit Windows.
The virtual address space is divided into two 2 GB partitions: one for use by the processes and other reserved for use by the system.
The address space (amount of physical memory) is limited to 2 GB for a 32-bit process.

In tuning SQL Server, there are 3 key things to look at: Physical Address Extension (PAE), Address Windows Extensions (AWE), &  4 Gigabyte Tuning (4GT).

Physical Address Extension (PAE) is a switch (/PAE) that is added to the boot.ini that allows an x86 processor to access and use more than 4GB of physical memory.  It's only used for 32-bit versions of Windows, as x64 processors can access more than 4 GB natively.  Most versions of Windows since 2000 Advanced Server support it.

4 Gigabyte Tuning (4GT) is a switch (/3GB) that is added to the boot.ini that increases the processes portion of the virtual address space from 2 GB to 3 GB.  4GT will limit the available RAM to 16GB, so if the server has more than 16GB RAM, 4GT should not be used. 

Address Windows Extensions (AWE) is a set of APIs that allows applications to access more than 4GB of RAM.

Which ones to use is determined by how much RAM your server has.  Here is a general rule of thumb for what to use and when:

Of course, doing research is part of the fun, so here are some references to keep handy:
Memory Limits for Windows Releases:  http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx
4GT:  http://msdn.microsoft.com/en-us/library/bb613473(VS.85).aspx
4GT Technical Reference:  http://technet.microsoft.com/en-us/library/cc778496(WS.10).aspx
AWE:  http://msdn.microsoft.com/en-us/library/aa366527(VS.85).aspx
PAE:  http://msdn.microsoft.com/en-us/library/aa366796(VS.85).aspx

Happy tuning!