Memory

This section looks at memory-specifically, memory on the server, including some of the issues associated with memory, the options you can use, and how they can impact the performance of the server. We'll start with a basic introduction to operating system memory, and then jump straight into the details of how to configure a server for different memory configurations.

Physical Memory

Physical memory is the RAM you install into the server. You are probably already familiar with the SIMMs and DIMMs that go into desktop PCs and servers. This is the physical memory, or RAM. This memory is measured in megabytes, or, if you are lucky, in terabytes, as the latest editions of Windows Server 2003 Data Center Edition now support systems with 1TB of RAM. Future editions of the operating system will increase this number as customers demand ever more powerful systems to solve ever more complex business problems.

Physical Address Space

The physical address space is the set of addresses that the processor uses to access anything on its bus. Much of this space is occupied by memory, but some parts of this address space are reserved for things such as mapping hardware buffers, and interface-specific memory areas such as video RAM. On a 32-bit processor, this is limited to a total of 4GB of addresses. On 32-bit Intel server processors with PAE, the address bus is actually 36 bits, which enables the processor to handle 64GB of addresses. You might assume that on a 64-bit processor the address bus would be 64 bits, but because there isn't a need for systems that can address 18 exabytes of memory yet, or the capability to build a system that large, manufacturers have limited the address bus to 40 bits, which is enough to address 1TB.

Virtual Memory Manager

The Virtual Memory Manager (VMM) is the part of the operating system that manages all the physical memory and shares it between all the processes that need memory on the system. Its job is to provide each process with the illusion that it has 4GB of virtual address space, and ensure that it has memory when it needs it, although the limited physical memory is actually shared between all the processes running on the system at the same time.

The VMM does this by managing the virtual memory for each process, and when necessary it will take back the physical memory behind virtual memory, and put the data that resided in that memory into the page file so that it is not lost. When the process needs to use that memory again, the VMM retrieves the data from the page file, finds a free page of memory (either from its list of free pages or from another process), writes the data from the page file into memory, and maps the new page back into the processed virtual address space. The resulting delay or interruption is called a page fault.

On a system with enough RAM to give every process all the memory it needs, the VMM doesn't have to do much other than hand out memory and clean up after a process is done with it. On a system without enough RAM to go around, the job is a little more involved. The VMM has to do some work to provide each process with the memory it needs when it needs it. It does this by using the page file to store data in pages that a process isn't using, or that the VMM determines it can remove from the process.

The Page File

On a server running SQL Server, the objective is to try to keep SQL Server running using just the available physical memory. SQL Server itself goes to great lengths to ensure that it doesn't over-allocate memory, and tries to remain within the limits of the physical memory available.

Given this basic objective of SQL Server, in most cases there is limited need for a page file. The only reason for changing the default size of the page file is to aid in diagnosing a system crash. In the event of a system crash, the kernel uses the parameters defined in the system recovery settings to decide what to do. You want the kernel to write some kind of memory dump, and in order to do this the page file size must be large enough to hold the type of memory dump you asked for. In servers with less than 2GB of RAM, a full memory dump is acceptable, so you need a page file that's at least 2GB. On servers with more than 2GB of RAM, a full memory dump can become excessively large, so choosing a smaller dump, such as a kernel dump, makes more sense. The smallest memory dump you can create is a mini-dump. A mini-dump has a lot less information than a kernel memory dump, but in many cases it may still have enough data to enable a system crash to be identified and resolved. If not, then a kernel dump, or in some cases a live kernel debug, is needed to isolate the root cause of the system crash.

In some cases, SQL Server and the OS might not cooperate well on sharing the available memory, and you may start to see system warnings about low virtual memory. If this starts to occur, then ideally add more RAM to the server, reconfigure SQL to use less memory, or increase the size of the page file. It's better to reconfigure SQL Server to remain within the available physical memory than it is to increase the size of the page file.

Page Faults

Page faults are generally bad for SQL Server, but not all page faults are the same. Some are unavoidable, and some have limited impact on performance, while others can cause severe performance degradation and are the kind we want to avoid.

SQL Server is designed to work within the available physical memory so that it won't experience the bad kind of page faults. Unfortunately, the Performance Monitor page fault counter doesn't indicate whether you are experiencing the benign or bad kind of page fault; and because of this, it doesn't tell you whether you are experiencing good or bad performance.

Soft Page Faults

The most common kind of page fault you will experience is the soft page fault. These occur when a new page of memory is required. Anytime SQL Server wants to use more memory, it asks the VMM for another page of memory. The VMM then issues a soft page fault to bring that memory into SQL Server's virtual address space. This actually happens the first time SQL Server tries to use the page, and not when SQL Server first asks for it. For the programmers among you, this means that SQL Server is calling VirtualAlloc to commit a page of memory. The page fault only occurs when SQL tries to write to the page the first time.

Hard Page Faults

Hard page faults are the ones you want to try to avoid. A hard page fault occurs when SQL Server tries to access a page of its memory that has been paged out to the page file. When this happens, the VMM has to step in and take some action to get the needed page from the page file on disk, find an empty page of memory, read the page from disk, write it to the new empty page, and then map the new page into SQL Server's address space. All the while, the SQL Server thread has been waiting. Only when the VMM has replaced the missing page of memory can SQL Server continue with what it was doing.

Why Page Faults Are Bad

If you look back to the section "Optimizing the Server," where I discussed the relative difference in speed between the CPU, memory, and disks, you can see that disk speeds can be as slow as 5MB/Sec, whereas memory speeds are likely to be around 2GB/sec. Whenever a hard page fault occurs, the thread that incurs the hard page fault will be waiting for a relatively long time before it can continue. If the thread running your query incurs a series of hard page faults, then your query will appear to run very slowly.

The most common symptom of this is intermittently slow-running queries. The resolution is to either add more memory to the server, reduce the memory used by other applications on the server, or tune your SQL Server queries to reduce the amount of memory the queries need.

Virtual Address Space

On a 32-bit system, each process running on the system has a total of 4GB of virtual address space (VAS). Note two important things about this: It's virtual memory, not physical memory, and it's only space, not actual memory.

The 4GB of VAS is shared between the kernel and your process, split in the middle at address 0x7FFFFFFFh, with user-mode address space from 0x00000000h through 0x7FFFFFFFh, and the kernel from 0x80000000h to 0xFFFFFFFFh. However, if you enable /3GB or /USERVA, the boundary can be moved from 2GB up to 3GB.

What this means is that there is no memory in the virtual address space until you either ask for memory or try to load something. In both cases, the OS takes your request and fills in a block of virtual address space with actual memory. Note that the actual memory isn't guaranteed to always be there, as the VMM can take the memory away and put it into the page file. This behavior is completely transparent; you know nothing except that the next time you try to access that piece of memory, it's very slow because you have to wait for the VMM to read the page from the page file, put it back into a page of memory, and then map that page back into your virtual address space.

32-Bit System Memory Configuration

Several options with 32-bit systems have been introduced over the years as ways to get around the basic limitations of a 32-bit system - that is, a 4GB address space evenly divided into 2GB of user address space and 2GB of kernel address space.

Physical Address Extensions

Intel introduced a way to get around the 4GB limitation of a 32-bit address bus by physically extending the address bus to 36 bits. This extension is called PAE, or Physical Address Extensions. It enables a 32-bit operating system to access up to 64GB of memory.

PAE is also a flag you can set in the boot.ini file to tell the operating system to use the version of the kernel that can take advantage of those extra four bits of address bus, and it enables a 32-bit Windows system to use more than 4GB of memory.

There are some scenarios in which you will end up running the PAE kernel even if you don't enable it in boot.ini. This is the case if you are running a DataCenter edition of the OS and the hardware is hot-swap-memory-enabled. In this case, because the server may have additional memory added at any time that could exceed the 4GB limit of 32-bit addresses, the OS always uses the PAE kernel, just in case it ever has to deal with more than 4GB of physical memory.

3GB

One way to increase the amount of memory a 32-bit process can use is to take some of the space assigned to the kernel and use it for user mode address space. You can do this by specifying either the /3GB or /USERVA options in the boot.ini file.

The /3GB option moves the boundary to be at 3GB, giving each process an additional 1GB of address space. This does mean that the kernel now only has 1GB of memory to use, however, which can sometimes be a problem that results in the server crashing with a bug check.

The /USERVA option enables you to specify a different amount of address space to be taken from the kernel. You can specify any value between 2GB and 3GB as the boundary between user and kernel address space. This has the same effect of increasing each process's virtual address space, and reducing the kernel's address space, as the setting /3GB, and can have the same consequences if the kernel ends up running out of memory space.

One of the limitations of reducing the amount of memory available for the kernel is that it reduces the amount of memory available for the kernel to track physical memory. This is why when you turn on /3GB or /USERVA, the OS is limited to using a maximum of 16GB of RAM. Therefore, if your server has more than 16GB of RAM installed, you wouldn't want to use /3GB or /USERVA.

AWE

Another way for a 32-bit process to use more memory is Address Window Extensions (AWE). AWE is a Windows API that enables a 32-bit process to create a small window in its virtual address space and then to use this to map physical memory directly into its virtual address space. This enables any 32-bit process to use more than 2 or 3GB of memory. SQL Server is a process that has been written to use the Windows AWE APIs. You can enable this on 32-bit editions of SQL Server by setting the SQL Server configuration option AWE enabled to 1.

Given what we know about memory, and AWE, it would seem that there is no reason to use AWE on a 64-bit system. After all, a 64-bit system has enough address space to address as much memory as it needs, so why would you consider using AWE on a 64-bit system? In fact, you can't enable AWE in a 64-bit version of SQL; the option is disabled in the UI.

However, it turns out that there are some great reasons for using AWE to access your memory, even on a 64-bit system. The SQL Server team realized that on 64-bit systems they could improve overall performance by using AWE in a 64-bit environment. They found that using AWE memory enables memory to be allocated a lot faster, and each access is faster. In addition, there is a more direct route to the memory, so every access is slightly faster.

Because of this, they changed the way 64-bit SQL Server works, so although you can't enable AWE on a 64-bit system, if the account used to run SQL Server has the "lock pages in memory" privilege, then SQL Server will automatically use AWE to access buffer pool memory.

/3GB or AWE?

This is a question you end up answering pretty frequently. A customer has a system with somewhere between 2GB and 4GB of RAM, and they want to know whether they should use /3GB, or AWE, or maybe both. One thing to remember is that this has to be a 32-bit operating system running 32-bit SQL server 2005. In any other scenario, this question just isn't relevant because SQL Server will have 4GB of virtual address space (32-bit SQL Server on an X64 OS), or it will have a 16 EB virtual address space (64-bit SQL Server on a 64-bit OS).

The /3GB option enables SQL server to use more memory for anything it needs. It can use the additional 1GB of virtual address space for the buffer pool, the procedure cache, or any other operation for which it needs memory.

AWE enables the SQL Server to use more memory, but only for the buffer pool. In most cases this is what SQL needs more memory for, but in some special cases the SQL may need more memory for the procedure cache, or connections, or something else that can't be allocated from the buffer pool.

These situations in which SQL can't use the extra memory for the buffer pool are very few and far between, but they do occur, so you need to know how to spot this situation. For more details, refer to Chapter 13, which includes ways to determine the amount of memory SQL Server is using for stored procedures versus the buffer pool.

64-bit Systems

This section applies to both X64 and IA64 systems. With X64 systems, you can install a 32-bit operating system. If you have installed a 32-bit operating system onto an X64 system, even though the processor is 64-bit capable, it's running as a 32-bit processor, so everything we said about 32-bit systems from the preceding section applies.

Each flavor of 64-bit operating system has a slightly different memory layout, and provides each process with a slightly different amount of virtual address space. This is currently 7,152GB for IA64, and 8,192GB with X64. Note that Windows Server 2003 SP1 currently supports a maximum of 1TB of physical memory.

What this means for SQL Server is that you don't have to worry about using PAE or /3GB as a workaround for the 2GB limit imposed by 32-bit Windows.

You do still have to consider AWE, though. This is because using AWE even in a 64-bit environment can provide you with a boost to performance at startup, when you need to take memory, and during normal operation. Using memory through AWE enables SQL to take a number of shortcuts and avoid going through the VMM. For these reasons, on a 64-bit system, if the account used to run SQL Server has the "Lock pages in memory" privilege, then SQL Server will default to using AWE memory.

Memory Configuration Scenarios

Now that you understand the various memory options, this section examines the key scenarios and makes specific recommendations regarding the various memory options:

• 32-bit OS and SQL with less than 2GB of RAM: This is the simple case. There isn't enough memory to require you to use PAE, /3GB, /USERVA, or AWE.

• 32-bit OS and SQL with 2–4GB of RAM: In this case, you don't need to use PAE, as you haven't exceeded the 32-bit address space limit. You might want to consider using /3GB, /USERVA, or AWE. This means either AWE or /3GB, not both, and the decision would depend on what the application is doing, which dictates what memory demands it will place on SQL.

  If you find that your 32-bit SQL Server system needs to use more than 2 to 4GB of memory, then rather than struggle with PAE, AWE, and /3GB, you should really be considering moving to a 64-bit system, which will provide immediate relief to many of the challenges we have to over-come in a 32-bit environment.

• Special case - 32-bit OS with 2 to 4GB of RAM: Use 3GB/ USERVA or AWE? A frequently asked question for customers in this space is whether should they use AWE to get to memory above the 2GB limit, or use /3GB or /USERVA to give SQL Server more memory. We have asked many people in the SQL Server developer team, and PSS, this question, and it seems that they all have a different answer. However, after asking a few more pointed questions, the answer becomes a little clearer.

  When SQL Server uses AWE memory, it can only be used by the buffer pool for data pages. In most cases this is just fine, as that's what you need the extra memory for. In a few very special cases, however, SQL Server needs to use the extra memory for other things, such as the stored procedure cache, the memory for user connections, or other internal memory that SQL needs to do its work. In these cases, SQL Server won't be able to use the extra memory if it's provided through AWE, so you would have to use /3GB.

• Special Case - 32-bit OS with 4GB of RAM: A couple of special cases aren't covered in the previous points. One of these is the odd situation that occurs when you actually try to fit 4GB of RAM into a 32-bit server. Initially this would seem to be pretty straightforward - after all, 4GB is the maximum amount of memory that a 32-bit operating system can address, so it should be just fine.

  Unfortunately, the operating system uses of some of that 4GB address space to do things such as map hardware into the address space, so before you even fit your first byte of RAM into the system, there is already a load of stuff there taking up some of your 4GB of available physical addresses. One of the biggest chunks of this memory is for mapping memory from video cards. On most server systems, there won't be a lot of memory here, but it's quite easy to take a reasonably mid-range workstation video card with 256 to 512MB of memory installed and slot that into a server. That's going to be another 256 to 512MB of your precious 4GB of addresses that have been eaten up by the video card's memory.

  What this means is that the operating system can't fit your 4GB of memory into the available space. To actually be able to use all your 4GB of memory, you need to enable PAE. This is the bit that seems so strange at first, until you look at the math and realize that you don't have to just account for the RAM you install, but for all those other areas of memory you never even thought about before. After you do the calculations, it becomes clear why you need to enable PAE to use all 4GB of RAM.

  To see this for yourself, open the Device Manager, switch the view to Resource View, and then expand the Memory node. You will see a window similar to the one shown in Figure 11-7.

  Initially this looks pretty confusing, but with a little explanation it starts to make more sense. The key to interpreting this window is to understand that most memory areas appear in this view multiple times. First there will be big chunks of memory allocated to the PCI bus. These areas are then duplicated in more detail, with specific memory allocations within each chunk of PCI bus memory. A good example of this is the area of memory shown in Figure 11-8.

  In this figure you can see three things mapped into the memory at 000A0000–000BFFFF: two named AGP graphics controllers, and a third mapping for PCI bus. All this is indicating is that this is the AGP graphics aperture, that there are multiple AGP cards installed, and that this area of memory is on the PCI bus.

  There is one more twist to this story. When enabling PAE as mentioned earlier, the BIOS must be able to map the physical memory into addresses above the 4GB limit. In some cases, the BIOS doesn't do this. In other cases, the hardware might not implement 36 address lines on the motherboard. Either of these reasons can prevent your server from mapping the additional memory above 4GB, preventing you from using a full 4GB of physical memory.

• 32-bit OS and SQL with 4 to 16GB of RAM: In this case, you need PAE to enable the system to access more than 4GB of RAM. You will also want to use AWE to enable SQL to access as much memory as possible. You might want to consider turning on /3GB as well.

• 32-bit OS and SQL with more than 16GB of RAM: Again, you need to use PAE to enable the OS to access more than 4GB of RAM. You also need to enable AWE to allow SQL to use all the additional memory above 2GB. You can't use /3GB in this case because it would restrict your memory to just 16GB.

• X64 system with 64-bit OS and 32-bit SQL with less than 4GB of RAM: If your system is running on a 64-bit OS, you don't need PAE because the OS can already access up to 16 exabytes of RAM. You don't need /3GB or /USERVA either, because on an X64 system every 32-bit process gets a full 4GB of user mode address space.

• X64 system with 64-bit OS and 32-bit SQL with more than 4GB of RAM: In this scenario, you still don't need PAE. You also don't need /3GB, or /USERVA. What you do need is AWE to enable SQL to use memory above 4GB. However, as you are running in a 32-bit process, although the server may have much more than 64GB of RAM available, your 32-bit process can only map a maximum of 64GB using AWE.

• 64-bit SQL on a 64-bit OS: In this case, SQL Server, like every other 64-bit process, gets 7152 to 8192GB of memory space by default. If the "lock pages in memory" privilege is granted, SQL will use AWE as a more efficient way for it to access memory than through the OS VMM.

Figure 11-7

Figure 11-8