Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Guide to Application Memory Tuning

By Paul Mu,

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

Total article views: 8808 | Views in the last 30 days: 9
 
Related Articles
FORUM

Memory Configuration and /3GB /PAE Switches

Memory Configuration and /3GB /PAE Switches

FORUM

/3GB Switch

/3GB Switch

FORUM

memory

memory

BLOG

Windows Tip - PAE - did you know that.....

Everyone remembers that in 32-bit windows architecture, in order for SQL Server (2005) to address mo...

FORUM

Memory

Memory

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones