Memory Fundamentals for SQL Server - Virtual Memory, /3GB Switch

Just a few days back I was asked a very basic question related to AWE and /3GB switch.I was surprised being a DBA I was unable to explain the concept and relate the terms by proper choice of words. That's when I realized how important it is to have this cleared prior to troubleshooting SQL server. You will be able to find lots of good books & blogs relating Windows & SQL Server memory management. The topic is quite vast to be covered in just a few blogs. I shall be giving the references of a few resources used in the last module of this series. My sole purpose of writing this blog is provide a gist of how SQL server is affected by the underlying windows memory architecture and what comprises SQL server memory.

Let's get started with the basics.

Difference Between 32-Bit and 64-Bit Systems

The largest integer value a 32-Bit pointer can store is  4,294,967,296= (2^32) addresses. Applications are limited to 4GB Virtual Address Space (VAS) . Of these 2GB addresses are  reserved for kernel mode and the other 2GB are reserved for user mode access.

If we were to have a 64-Bit pointer the largest inter value it could store would be roughly 17 trillion GB =   2^64 addresses. The number of addresses are so huge that they are not implemented in reality in terms of physical memory due to hardware limitations. Currently the processor manufacturers have implemented a 44-bit pointer that can refer to 16TB Virtual Address Space for 64-bit systems. Out of this 8TB is reserved for kernel mode and 8TB for user mode access.

Virtual Memory

The total amount of RAM (Random Access Memory) installed might not always be sufficient to run all the applications on the system. Windows memory management implements and manages what is called Virtual Memory. It’s the method of extending the available physical memory on a computer. PageFile is a file on disk which is used to swap physical memory pages to and from a disk. Virtual Memory comprises of total available physical memory(RAM) on the computer and size of the PageFile on disk.

Virtual Memory Manager

Virtual Memory Manager is responsible for mapping physical memory and virtual address spaces for a process. It keeps track of each mapping for VAS addresses using a page table, and the mapping information itself is stored in a Page Table Entry (PTE). The PTE is 4K in size and shares the same space as other system information and structures of the system.

Most recently referenced pages are located in RAM. Those which aren't recently referenced for a while may be written to the PageFile. This is called swapping or paging out memory.

Thus there is no gaurantee that once a particular page was accessed from certain location within memory, the same page is available at the same location the next time its accessed. The next time it may be loaded from the PageFile on disk so the request will be slower. This  is known as a Page Fault and it occurs without users intervention or knowledge.The process of loading the page back from the PageFile into physical memory, also called swapping or paging in memory.

/3GB switch in 32-bit systems

As explained earlier the primary difference between 32-bit and 64-bit systems is the size of the address pointer and the number of addresses referenced in terms of memory. As we know in 32-bit systems 2GB is reserved for user mode access. We can increase this memory to 3GB for SQL server process address space thus reducing the kernel mode access space to 1GB by implementing the /3GB switch. A /3GB parameter needs to be specified  in the Boot.ini file located under C: drive ideally of Windows 2003 or earlier version. Once you add the parameter and reboot each process will have a 3GB address space.

How to Enable  /3GB switch:

1.      Right-click on the Command Prompt icon in the Accessories program group of the Start menu. Click Run as Administrator.

2.      At the command prompt, enter: bcedit /set IncreaseUserVa 3072

3.      Restart the computer.

You could browse this link to check how the /3GB option is enabled.https://technet.microsoft.com/en-us/library/bb124810(v=exchg.65).aspx

In the next module we will learn about AWE and /PAE switch.