SQL Server’s memory usage often leads to confusion, particularly when AWE memory is involved.
When the amount of memory available to SQL Server is under 3-4GB, SQL Server (or any other 32-bit application) can reside entirely in the Virtual Address Space (VAS), which is a virtual 32 bit address space allowing each process to use up to 4 GB of memory – 2 GB for user-mode and 2 GB for the system (kernel mode). This virtual address space is then managed by Windows’ memory management to map it into real memory, and SQL Server can use up to 2 GB of memory.
A 32 bit machine with 4 GB of RAM can use the /3gb switch in boot.ini to allow user mode (and thus SQL Server) VAS to use 3 GB, limiting the kernel mode to 1 GB.
If you look at Task Manager, you can see exactly how much memory SQL Server is using when SQL Server is entirely in VAS.
However, what happens when your machine has more than 3 GB of memory? The answer is AWE, or Address Windowing Extensions. AWE allows the operating system to map in a window of the extra memory, up to the limit of the operating system, which is 64 GB for Windows Server 2003, Datacentre Edition. The SQL Server process will still require around 256 MB of memory to implement AWE, but the buffer and procedure cache can then be stored in AWE-mapped memory. When looking in Task Manager, SQL Server will only show the 256 MB of memory, and the AWE-resident memory is hidden. The best way to determine SQL Server’s AWE memory usage is via the SQLServer: Memory Manager – Total Server Memory (KB) Perfmon counter.
To turn on AWE, there are three steps:
- Add the /pae switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory
- Grant “Lock Pages in Memory” to the user account that runs the SQL Server process
- Set the configuration setting “AWE Enabled” to 1.
Note that AWE requires 2 GB of kernel VAS to address AWE memory above 16 GB, so using the /3gb switch in boot.ini will limit AWE to 16 GB.
Under Windows 2000, AWE will only be enabled if the available physical memory is greater than the user mode virtual address space. When SQL Server starts, it will lock either the amount of memory specified in the configuration setting “Max Server Memory”, or the amount of free physical memory, minus 256 MB.
This AWE mapped pool cannot be swapped out of physical memory. Also, the value of “Min Server Memory” is not used, as the AWE allocated by SQL Server is never decreased in Windows 2000.
Under Windows Server 2003, SQL Server will always try to use AWE if it is enabled, and SQL Server can dynamically manage its AWE memory usage. Upon startup, it will allocate the amount of memory needed for the startup workload, and from there it will grow as needed, assuming memory is available. It will continue growing up to the value specified in “Max Server Memory”. If the server comes under memory pressure, SQL Server 2005 will release memory if possible, down to the point specified in “Min Server Memory”.
The 32-bit versions of Windows Server 2003 support the following maximum memory limits: Standard Edition 4 GB, Enterprise Edition 32 GB, and Datacentre Edition 64 GB.
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 ~2 GB is a good setting, for under 32 GB total server memory, ~4 GB if 64 GB total server memory. 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.
AWE is not necessary on a 64-bit machine, as the operating system’s memory limits are the limits (a 64 bit memory address can point to 16,777,216 GB of memory). However, it is recommended that the “Lock Pages in Memory” permission is granted to the user account that runs the SQL Server process, as this will prevent SQL Server’s memory from getting paged out to disk.