While researching a problem with AWE allocating memory in SQL Server (see my previous post Using Address Windowing Extensions (AWE) with SQL Server), I found myself deep-diving into the Windows memory management model. I’d like to share my findings here to help those tasked with setting up a SQL Server instance (either for BizTalk or any other application), implement the best possible system memory configuration.
In this two part post I’ll cover the various memory management options available, to ensure you get the most out of your physical memory / operating system choice, before moving on to review SQL Server options in Part II. Please note that the following information relates to a 32-bit architecture, unless otherwise stated – I plan on posting a similar article on the 64-bit architecture sometime in the near future.
Virtual Memory and Memory Management
On the x86 family of processors, all processes are provided with 4Gb of virtual memory. By default, the first 2Gb is allocated to the operating system kernel and the latter 2Gb to the user process. This virtual memory isn’t real ‘physical’ memory – as processes make memory allocations, physical storage is provided, mixed between physical memory and the system paging file*. Windows transparently handles copying data to and from the paging file, so that the application can allocate more memory than physically exists in the machine and, so multiple applications can have equal access to the machine’s physical RAM.
* (this is how multiple applications can run on a system with 512Mb of RAM, each with a virtual address space of 4GB – it’s not real memory, but it seems like it to the application)
Tuning Virtual Memory
Windows NT 4.0 introduced the concept of the /3Gb switch (added to the [operating systems] section of the boot.ini file), which allows system administrators to modify the allocation of virtual memory between the OS and user processes. By adding the /3GB switch (and restarting), Windows will allocate just 1Gb to the kernel mode address space, allowing a process’s user mode address space to increase to 3Gb.
In addition to the /3GB switch, Windows XP, Windows Server 2003 & 2008 includes the /USERVA switch. This allows finer granularity over the amount of virtual memory provided, to the kernel mode and user mode address space. To use the /USERVA switch, simply indicate how much memory should be provided for user mode address space. For example, /USERVA=2560 configures 2.5GB for user mode space and leaves the remaining 1.5GB for the kernel. When using the /USERVA switch, the /3GB switch must also be present.
To add either the the /3GB or /USERVA switches, go to System Properties -> Startup and Recovery and click Edit under System Startup. Once reconfigured, you’re [operating systems] section should look something like this:
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB /USERVA=2560
Utilising all Physical Memory: Physical Address Extension (PAE)
PAE support was added by Intel starting with the Pentium Pro family and later* and provides a memory-mapping model, which allows Windows to access to up to 64Gb of physical memory, rather than the standard 4Gb. In PAE mode, the memory management unit implements page directory entries (PDEs) and page table entries (PTEs) that are 36-bits wide (rather than the standard 32-bits) and adds a page directory pointer table to manage these high-capacity tables and indexes into them, allowing the operating system to recognise up to 64Gb.
In practice, this means that although Windows processes are still given a 4Gb allocation of virtual memory (virtual memory is still allocated using 32-bit pointers, limiting their maximum size to 4Gb), multiple processes can immediately benefit from the increased RAM as they are less likely to encounter physical memory restrictions and begin paging.
A specific version of the Windows kernel is required to use PAE, either Ntkrnlpa.exe for uniprocessor systems, or Ntkrpamp.exe for multiprocessor systems, both are located in the \i386\Driver.cab file. No additional work needs to be undertaken by the system administrator, apart from adding the /PAE switch in a similar fashion to the /3GB or /USERVA switches. If however you are running hardware that supports hot-adding memory , the PAE switch will be added by default (hot-add memory is only supported by Windows Server 2003 Enterprise and Datacenter editions). Note: 64-bit versions of Windows do not support PAE. The PAE switch can be added with or without the /3GB switch, as detailed later.
To manually add the PAE switch add the following to your boot.ini file:
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE
The following table details the maximum physical memory that a Windows version can recognise, with the PAE switch enabled:
- Windows 2000 Server – 4Gb Maximum
- Windows 2000 Advanced Server – 8Gb RAM Maximum
- Windows 2000 Datacenter Server – 32Gb RAM Maximum
- Windows Server 2003 Web Edition – 2Gb RAM Maximum
- Windows Server 2003 Standard Edition – 4Gb RAM Maximum
- Windows Server 2003 Enterprise Edition – 32Gb RAM Maximum
- Windows Server 2003 Datacenter Edition – 64Gb RAM Maximum
- Windows Server 2008 Web Edition – 4Gb RAM Maximum
- Windows Server 2008 Standard Edition – 4Gb RAM Maximum
- Windows Server 2008 Enterprise Edition – 64Gb RAM Maximum
- Windows Server 2008 Datacenter Edition – 64Gb RAM Maximum
- Windows Server 2008 Datacenter Edition (Server Core) – 64Gb RAM Maximum
Unless you have a system with more than 4Gb of physical memory, there is little point in enabling PAE; however, PAE can be enabled on Windows XP SP2, Windows Server 2003 and later 32-bit versions of Windows, to support hardware enforced Data Execution Prevention (DEP).
I’ve provided only a brief overview of the Physical Address Extensions here; for more background reading please see the following: Microsoft KB articles 283037 and 268363, Windows Hardware Developer Central article Physical Address Extension – PAE Memory and Windows.
* the PAE extension is supported on AMD chipsets, although I can’t find any hard evidence on the AMD website.
Addressing Windows Extensions (AWE)
Unlike the PAE switch, the AWE facility in Windows exists to allow applications – such as SQL Server – to access more than 4GB of physical memory. AWE removes the 4Gb physical memory limit of 32-bit software architecture by enabling code to allocate large chunks of physical memory and then, map access to the physical memory into a window of virtual memory that is 32-bit addressable. Because AWE allows the OS to allocate memory above the 4Gb boundary, there is little point enabling it on a system with 4Gb or less of physical RAM.
One thing to note with AWE memory is that it is never swapped to the system paging file (i.e. disk). If you review the AWE API, you’ll see that the methods refer to physical memory allocation: AWE memory is physical memory that is never swapped to or from the system paging file. This explains why that in order to use the ‘Use AWE to Allocate Memory’ flag in SQL Server, requires the ‘Lock Pages in Memory’ Local Security Policy setting (see Using Address Windowing Extensions (AWE) with SQL Server) – pages can only be locked in memory if this local security policy is set. This also explains why (or how) applications such as SQL Server, Exchange etc. when using AWE to allocate memory can consume such great amounts of physical RAM.
Best Practice Configurations
Based on the information provided above, Microsoft recommend the following physical memory / operating system memory switch combinations:
- 4Gb Physical RAM – /3GB switch (or /USERVA switch)
- > 4Gb Physical RAM – /3GB and /PAE switch
- > 16Gb Physical RAM – /PAE switch
A Final Note about the /3GB Switch
You will notice that in the table above recommends that a server with greater than 16Gb of physical RAM should not be configured with the /3GB switch. When you apply the /3GB switch, you limit the size of the virtual memory address space available to the kernel to 1Gb (from the usual 2Gb), which is too small for the virtual memory manager to store the memory mapping tables needed to access more than 16Gb of RAM. As a result, the memory manager imposes a virtual memory limit of 16 GB on a system with both the /3GB and /PAE enabled. Even if a system has 32 GB or more of physical memory, if both options are enabled, only 16 GB of memory will be recognised.
However, although 16Gb is a hard upper limit imposed by the kernel, most workloads will actually show decreased throughput on systems with 12Gb of memory, and many on systems/workloads with as low as 8GB of memory. Therefore, ensure you thoroughly test the use of the /3GB switch within you UAT/reference environment before applying it to live systems.
I’m going to close Part I here because there is plenty for the reader to take onboard before we start to look at how these considerations affect SQL Server. Part II will be out in the next few days.
- Comparison of Windows Server 2003 Editions
- RAM, Virtual Memory, Pagefile and all that stuff
- Protecting RAM Secrets with Address Windowing Extensions
- PAE and /3GB and AWE oh my…
- Memory Limits for Windows Releases
- Do I have to assign the Lock Pages in Memory privilege for Local System?
- Inside SQL Server 2000′s Memory Management Facilities
- Lock escalation in SQL Server 2005
- Large Memory Support – 4-Gigabyte Tuning
- Myth: PAE increases the virtual address space beyond 4GB
- Hot-Add Memory Support in Windows Server 2003
Please read my disclaimer in relation to this post.