This article will provide the DBA with appropriate background information and links to resources in order to have a good understanding of application memory tuning, with specific application to SQL Server 2000 EE under Windows Server 2003.

Lets start by defining some terms that are important to our discussion. A process is an instance of a running application. A physical storage consists of the physical memory (RAM) and system paging files. A virtual memory is defined as a range of address space (or pointers) with no associated physical storage.

For a 32-bit Windows operating system, Windows allocates all processes a 4GB address space. This 4GB limit is because a 32-bit pointer can have a maximum of 2^32 values, whereas a 64-bit system will be able to handle 2^64 values, a very large number indeed! The 4GB address space is divided into two partitions, a user mode partition and a kernel mode partition. By default, each of these is sized at 2GB. It is however possible for a process to use more than the default 2GB of addressable memory. The /3GB switch offers such an option.

The /3GB Switch

The /3GB switch, when configured in Windows, will expand the user mode address space for a process from 2GB to 3GB. The down-side is that the kernel mode address space must also decrease to 1GB in order to preserve the 4GB limit for a 32-bit OS. To configure this in Windows, add the /3GB switch to the appropriate line in the BOOT.INI file, or follow the steps in this link for Windows Server 2003 (http://support.microsoft.com/kb/317526).

The /3GB switch is only applicable to systems having between 2GB and 4GB of configurable RAM. Where more than 4GB of memory is available, Windows can be configured to use the /PAE switch instead.

The /PAE Switch

PAE stands for Physical Address

Extension, and according to Microsoft 'PAE is the added ability of the IA32 processor to address more than 4 GB of physical memory'. To enable PAE you add the /PAE switch to the appropriate

line in the BOOT.INI file.

It is also

possible to use the /PAE switch in conjunction with the /3GB switch. However,

in this instance, the maximum memory that windows can manage is limited to 16

GB due to the kernel mode partition being reduced to only 1GB of

addressable memory.

Not all Windows

OS support these switches. According to the following article (http://support.microsoft.com/?kbid=291988), the /3GB and /PAE switches in the Boot.ini file are to be used with the following products: Windows 2000 Server (Advanced and Datacenter editions) and Windows Server 2003 (Enterprise, Datacenter and Small Business editions). Other scenarios where the switches are supported are for testing purposes only.

Configure SQL Server to Use More Memory

According to Microsoft AWE is

a set of APIs to the memory manager functions that

enables applications to address more memory than the 4 GB that is available

through standard 32-bit addressing. The important thing to note from this statement is that AWE

is related to a process (or application), and that not all applications or

versions of an application are AWE-aware.

Further information on the

particular version of SQL Server and the underlying Windows Operating System

that support this type of memory configuration, as well as instructions on how

to enable AWE on SQL Server, can be found in this website (http://support.microsoft.com/?id=274750).

For SQL Server 2000 EE with SP4, there is a fix for AWE which

you can download from this website (http://support.microsoft.com/?kbid=899761).

Once you have enabled AWE on your

SQL Server, you can set an appropriate min/max server memory thresholds.

Understand that if the operating system does not have enough

memory it will request memory from SQL Server, thus setting an inappropriate

min/max server memory will impact on the performance of SQL Server. For a

dedicated database server, the default memory settings for SQL Server should

suffice. Note that a value of 0 for min server memory simply means that SQL

Server will dynamically manage its memory.

Conclusion

For certain applications running under various Windows operating system, it is possible to tune the memory used by both the OS and the application. For the OS, the two configurable switches are the /3GB switch and the /PAE switch. Once configured, an AWE-aware application (such as SQL Server 2000 EE) can be enabled to access memory in excess of 4GB.

References

A highly recommended book for the serious DBA is The Gurus Guide to SQL Server Architecture and Internals by Ken Henderson.

Further information on Memory Configuration Options for SQL Server can be found at this website: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_2f3b.asp

Further information on PAE can be found at this Microsoft support website:http://support.microsoft.com/default.aspx?scid=kb;en-us;283037