INF: Recommended SQL Server for NT Memory Configurations

  - Microsoft SQL Server, versions 4.2, 6.0 and 6.5

SUMMARY

Microsoft SQL Server allows the use of up to 2048 MB of virtual memory. This article discusses how much memory you should allocate to SQL Server in various machine memory configurations.

MORE INFORMATION

Windows NT provides each 32-bit Windows application a 4-gigabyte (GB) virtual address space, the lower 2 GB of which is private per process and available for application use. The upper 2 GB is reserved for system use.

The 4-GB address space is mapped to the available physical memory by the Windows NT Virtual Memory Manager (VMM). The available physical memory can be up to 4 GB, depending on hardware platform support.

A 32-bit Windows application such as SQL Server only perceives virtual or logical addresses, not physical addresses. How much physical memory an application uses at a given time (the working set) is determined by available physical memory and the VMM. The application cannot directly control memory residency.

Virtual address systems such as Windows NT allow the over-committing of physical memory, such that the ratio of virtual to physical memory exceeds 1:1. As a result, larger programs can run on machines with a variety of physical memory configurations. However, in most cases, using significantly more virtual memory than the combined average working sets of all the processes will result in poor performance.

Thus, configuring SQL Server for more virtual memory than the amount of physical memory that exists can result in poor performance. Also, the Windows NT operating system memory requirement must be considered--about 12 MB with some variation depending on application-induced overhead. Note that as SQL Server parameters are configured upward, this system overhead requirement can grow as Windows NT needs more resident memory to support additional threads, page tables, and so forth.

This results in a varying amount of memory that can be given to SQL Server depending on the machine memory configuration. The following table presents a rough estimate of memory configurations and assumes a dedicated database server. If the machine is shared among multiple uses such as a file server, database server, and/or a client workstation, less memory should be given to SQL Server and more left for the operating system and other uses.

Note these values are only rough figures, and are presented to give an approximate idea of SQL Server memory allocation over different memory states. For more information you can use the many monitoring features of Windows NT Performance Monitor (Perfmon) to determine your system memory behavior. A good source of information is Volume 3 of the Windows NT Resource Kit, "Optimizing Windows NT," by Russ Blake, [ISBN 1-55615-619-7], which devotes nearly 600 pages to various aspects of monitoring and optimizing Windows NT and 32-bit Windows applications.

   Machine Memory          Approx. Memory To Give SQL Server

     16 MB                       4 MB
     24 MB                       8 MB
     32 MB                      16 MB
     48 MB                      28 MB
     64 MB                      40 MB
     128 MB                    100 MB
     256 MB                    216 MB
     512 MB                    464 MB

The point at which additional memory fails to provide worthwhile benefits is entirely situation-dependent, and is determined primarily by the locality of reference of the database accesses. The important point to remember is that memory increases that are relatively small as a percentage of total memory rarely afford any significant benefit. Two things control this: SQL Server uses extra memory primarily as buffer cache; and most cache hit ratio studies indicate a fairly flat curve beyond several megabytes.

For this reason, on a 32-MB machine, whether 14 MB, 16 MB, or 18 MB is given to SQL Server, it will rarely make a significant difference in SQL performance. Conversely, attempting to "crowd" Windows NT by giving excessive memory to SQL Server can result in poor performance because of excessive paging.

The implication is that you should add physical memory to the machine in significant amounts before allocating this to SQL Server. Whether or not adding memory will be beneficial should be studied beforehand. The easiest way to determine this is by using Perfmon to check the SQL Server cache hit ratio while the system is under a typical load. If the hit ratio is relatively high (over 90%), adding more memory usually will not be beneficial. This is because additional memory can mainly be used for additional SQL Server data cache, thereby increasing the hit ratio. In this case, the hit ratio is already high, and the maximum available improvement quite small.

If the hit ratio is consistently lower than this, adding more memory may improve the hit ratio and thereby performance, if the locality of reference is such that it can be "bracketed" by economically or technically feasible amounts of memory.