Using SQL Server with Large Amounts of Memory

What's the best way to configure SQL Server to handle large amounts of memory, and how do I know when to use the /3GB switch?

Many customers are still confused about how to configure SQL Server when large amounts of memory are installed on the machine. I don't have the space to fully explore this topic here; however, I'll try to clear up some basic misunderstandings and point out a best practice for determining when to use the /3GB switch. I also strongly encourage you to read the Microsoft article, "How to configure memory for more than 2 GB in SQL Server," at http://support.microsoft.com/default.aspx?scid=kb;en-us;274750. The article references several other articles that I recommend if your server has more than 4GB of memory.

Let's start with the basics. First, 32-bit OSs can directly address up to 4GB of memory. That is, 32 bits can represent 2³² or 4,294,967,296 different addresses or locations in memory where data can be stored. But 1 GB of memory equals 1,073,741,824 bytes, so 4,294,967,296 bytes represents 4GB of addressable memory. However, 32-bit SQL Server can access more than 4GB of memory on a server by using Physical Address Extensions (PAE) that create windows into a larger memory space. For example, if your server has 32GB of memory, you have eight logical windows of 4GB each that are mapped by the OS in and out of the 4GB set of addresses that can be directly addressed by a 32-bit OS. You can enable this ability to access more memory by placing the /PAE switch in the boot.ini file. You also have to make some configuration changes in SQL Server to enable Address Windowing Extensions (AWE) memory. The Microsoft article discusses both steps.

By default, the 4GB of memory that 32-bit Windows can address is divided equally between the OS and an application process. This means that by default, Windows will use 2GB of memory for internal housekeeping and the SQL Server process, for example, can address the remaining 2GB. However, you can limit the OS to 1GB and tell Windows that the application process gets 3GB of addressable space. As the Microsoft article explains, you do this in Windows by placing the /3GB switch in the boot.ini file, which is a protected OS file. This change will affect all applications running on the server.

Whereas most people understand these basics, they don't always comprehend the interaction of the /PAE and /3GB switches. It's impossible to use more than 4GB of physical memory in the server unless you enable the /PAE switch. However, you can use the /3GB switch to let SQL Server use more than 2GB on a server that has 4GB of memory. You can enable both the /3GB and /PAE switches at the same time. In this case, the window that will map SQL Server memory addresses into and out of the directly addressable 4GB memory space will be 3GB rather than 2GB.

SQL Server Books Online (BOL) says you should enable the /3GB switch when you use AWE on a server that has less than 16GB of memory. But don't enable, the /3GB switch on servers that have more than 16GB of physical memory because as the physical memory in the server increases, the OS requires more memory for internal housekeeping tasks. Jasper Smith, a SQL Server MVP, recently told me that Microsoft and certain hardware vendors have modified this recommendation. Some top SQL Server experts from Microsoft advise customers to use the /3GB switch only on servers with 12GB of physical memory or less, which contradicts the 16GB limit that BOL—and numerous other Microsoft sources—specify. Various people at Microsoft have said that the new recommendation is based on testing with high-end systems that showed that the OS typically begins to need more than 1GB of memory for housekeeping after you install 12GB or more of physical memory on the server. Microsoft hasn't put this recommendation into print to the best of my knowledge.