INF: Recommended SQL Server for Windows NT Memory Configurations

• Microsoft SQL Server versions 4.2x, 6.0, 6.5

SUMMARY

Microsoft SQL Server allows the use of up to 2,048 MB of virtual memory. This article discusses how much memory you should allocate to SQL Server versions 4.2x, 6.0, and 6.5 in various computer memory configurations. For information about memory configuration in SQL Server version 7.0, please refer to Microsoft Books Online, included with SQL Server version 7.0.

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 computers 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 computer memory configuration. The table below presents a rough estimate of memory configurations and assumes a dedicated database server. If the computer 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 that 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 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.

Computer 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
1 GB	950 MB
1.5 GB	1300 MB
2 GB	1500 MB

Because Windows NT allocates additional resources for each thread spawned (for example, a 1 MB stack is allocated per thread), SQL Server should rarely be configured to use more than 1500 MB, even on systems with 2 GB or more of physical memory. Attempts to do so may cause unpredictable behavior when all memory within the process' 2 GB virtual address space have been consumed. On appropriately configured systems running SQL Server Enterprise Edition, where the available virtual address space is expanded to 3 GB, more memory can be configured for SQL Server. See the SQL Server Enterprise Edition documentation for guidelines on memory settings on these systems.

The minimum amount of available memory for a SQL Server with an Intel-based processor is 16 megabytes (MB). SQL Server for RISC platforms will require more memory because of the average lower density of RISC computer instructions. However, considering the overall software, hardware, application, and personnel investment in typical client/server systems, adding more memory is usually a wise, and by comparison, inexpensive investment. Many sites report that 32 MB is a good starting point, and it is not uncommon for servers to be configured for 128 MB or more memory, which they put to beneficial use.

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 computer, whether 14 MB, 16 MB, or 18 MB is given to SQL Server, it will rarely make a significant difference in SQL Server 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 computer in significant amounts before allocating it to SQL Server. Whether or not adding memory will be beneficial should be studied beforehand. The easiest way to determine this is by using Windows NT Performance Monitor 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 percent), 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.

REFERENCES

Q115050 INF: When to Use Tempdb In RAM

Additional query words: 4.20 sql6 sqlfaqtop perfmon machine machines

Keywords : kbenv SSrvAdmin
Version : winnt:4.2x,6.0,6.5
Platform : winnt
Issue type : kbinfo