Optimising System Memory for SQL Server – Part I

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:

[operating systems]
multi(0)disk(0)rdisk(0)partition(1)WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB

[operating systems]
multi(0)disk(0)rdisk(0)partition(1)WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB /USERVA=2560

More information on the /3GB and /USERVA switches can be found in the Microsoft KB articles 316739 and 810371.

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 i386Driver.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:

[operating systems]
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 Serverto 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.

References

  1. Comparison of Windows Server 2003 Editions
  2. RAM, Virtual Memory, Pagefile and all that stuff
  3. Protecting RAM Secrets with Address Windowing Extensions
  4. PAE and /3GB and AWE oh my…
  5. Memory Limits for Windows Releases
  6. Do I have to assign the Lock Pages in Memory privilege for Local System?
  7. Inside SQL Server 2000′s Memory Management Facilities
  8. Lock escalation in SQL Server 2005
  9. Large Memory Support – 4-Gigabyte Tuning
  10. Myth: PAE increases the virtual address space beyond 4GB
  11. Hot-Add Memory Support in Windows Server 2003

Please read my disclaimer in relation to this post.

About these ads

13 thoughts on “Optimising System Memory for SQL Server – Part I

  1. Excellent!!! Where is Part II? So, according to the AWE section, if AWE is enabled on SQL, SQL will not use the Page File virtual memory?

  2. I am having an issue with memory. I am running win 2003 server with 8gb of memory. I am also running
    SQl 2005. My system will not allow me to do anything because I keep getting memory issue problem.

    What do you think is causing the problem?

    • Hi Nebs.
      A quick Google reveals that the /PAE switch is not supported on 64-bit Microsoft platforms, so there isn’t much point in enabling the switch. FYI, a 64-bit machine has (theoretically) the capability of addressing 264 bytes (or 16 exbibytes) which is considered practically unlimited, being far more than the total amount of RAM ever manufactured.

  3. Hi Nick,

    This is one of the best articles I have read on SQL Server Memory configuration optimisation!

    I hope you do not mind but I have linked to your article from my site.

    Cheers,
    John

  4. hi,
    We use 32 Bit OS (Adv Svr Win 2003) Where about 16GB Physical Memory is available…

    But when we look at the Processes in the task manager, only about 4 GB of the same is being Used. How do we make use of the other 8 GB in the server.

    We use SQL server 2005 for our activities where it is only using upto 2 GB of space…

    mahesh.velupula@relianceada.com

    Pl write to us back on the above specified EMAIL ID with any Info at all which could help in reviving the server Speed.

    Rgds
    Mahesh
    Bangalore-INDIA

  5. We are running a SQL server 2005 standard edition 32 bit software. My OS is Windows Server 2008 R2 64 bit with a 16 GB RAM. While looking into Task Manager we find that SQLServer.exe doesn’t utilize more than 3.5GB where as we want it to consume upto 10 GB of memory.

    Can someone help?

    Thanks in advance,
    Vikas

  6. Win Server 20003 enterprise, sql server 2000 enterprise and 16Gb memory /PAE activated and AWE activated (on sql server with 12Gb dedicated) but sql server seems to used only 1.8Gb, and spotlight for win give me an alert ”
    Process 1644: ‘sqlservr’ has a virtual address space of 1864,69 MB. This is close to the Windows two gigabyte address space limit

    I want to put the /3Gb switch but the AWE switch must be desactivated?

    RMQ: in the Best Practice Configurations section you do not specify when AWE switch must be activated.

    Rgds

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s