How to configure Windows Server 2003 Enterprise and SQL 2005 to use more then 4GB RAM
32-bit processors have a limitation of only being able to directly address 4GB RAM – their architecture was never designed to address more. 64-bit processors get around that limitation by being able to us 64 bits to address RAM (potentially 16,777,216 GB), but what do you do if you have an application that won’t work on a 64-bit copy of Windows, but does need to utilise more than 4GB of RAM?
The answer is to use PAE (Physical Address Extensions) and AWE (Application Windowing Extensions). I blundered through PAE a little while back, and found that it didn’t work as expected because I was using Server 2003 Standard. PAE is only available as part of Server 2003 Enterprise and Datacenter edition.
Back to the problem at hand, I have a memory hungry application that sits on a Windows Server 2003 Enterprise box with SQL server 2005 installed.
Firstly, we need to enable PAE to allow the 32-bit operating system to address memory over the 4GB limit. This is configured by adding the /PAE switch to the boot.ini.
We also need to repartition the 4GB of Virtual Address Space (VAS) that 32-bit Windows can address by using the /3GB switch in the boot.ini. This allows 3GB of RAM to processes running in “Application Mode” and 1GB RAM to the “Kernel Mode”. If you’re using more the 16GB of memory don’t use the /3GB switch as PAE/AWE will need 2GB of RAM in the Kernel Mode.
The memory intensive applications will run in, you guessed it, Application Mode and will therefore be able to utilise the extended memory provided through PAE and AWE.
Since SQL server will run in the Application Mode memory partition and is AWE aware, it can be configure to reside entirely in AWE managed memory.
The user account that is used to run SQL server must be granted the “Lock Pages in Memory” right and the “AWE Enabled” setting in the configuration of SQL server must be set to “1”. We also need to set the “Max Server Memory” to stop SQL server consuming all the AWE memory available. The “Min Server Memory” does not need to be configured as AWE memory is not released by SQL server.
Once that is all configured and a reboot applied, SQL server should only consume about 256mb in task manager, the rest having been loaded into AWE memory and not viewable from the Task manager.