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.
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 andavailable 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 theWindows NT Virtual Memory Manager (VMM). The available physical memory canbe up to 4 GB, depending on hardware platform support.
A 32-bit Windows application such as SQL Server only perceives virtual orlogical addresses, not physical addresses. How much physical memory anapplication uses at a given time (the working set) is determined byavailable physical memory and the VMM. The application cannot directlycontrol memory residency.
Virtual address systems such as Windows NT allow the over-committing ofphysical memory, such that the ratio of virtual to physical memory exceeds1:1. As a result, larger programs can run on computers with a variety ofphysical memory configurations. However, in most cases, using significantlymore virtual memory than the combined average working sets of all theprocesses will result in poor performance.
Thus, configuring SQL Server for more virtual memory than the amount ofphysical memory that exists can result in poor performance. Also, theWindows 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 overheadrequirement can grow as Windows NT needs more resident memory to supportadditional threads, page tables, and so forth.
This results in a varying amount of memory that can be given to SQL Serverdepending on the computer memory configuration. The table below presentsa rough estimate of memory configurations and assumes a dedicated databaseserver. 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 anapproximate idea of SQL Server memory allocation over different memorystates. For more information, you can use the many monitoring features ofWindows NT Performance Monitor to determine your system memorybehavior. A good source of information is Volume 3 of the Windows NTResource Kit, "Optimizing Windows NT," by Russ Blake, [ISBN 1-55615-619-7],which devotes nearly 600 pages to various aspects of monitoring andoptimizing 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 willrequire 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, inexpensiveinvestment. Many sites report that 32 MB is a good starting point, and itis 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 benefitsis entirely situation-dependent, and is determined primarily by thelocality of reference of the database accesses. The important point toremember is that memory increases that are relatively small as a percentageof total memory rarely afford any significant benefit. Two things controlthis: SQL Server uses extra memory primarily as buffer cache; and mostcache hit ratio studies indicate a fairly flat curve beyond severalmegabytes.
For this reason, on a 32-MB computer, whether 14 MB, 16 MB, or 18 MB isgiven to SQL Server, it will rarely make a significant difference in SQL Server performance. Conversely, attempting to "crowd" Windows NT by givingexcessive memory to SQL Server can result in poor performance because ofexcessive paging.
The implication is that you should add physical memory to the computer insignificant amounts before allocating it to SQL Server. Whether or notadding memory will be beneficial should be studied beforehand. The easiestway 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 mayimprove the hit ratio and thereby performance, if the locality of referenceis such that it can be "bracketed" by economically or technically feasibleamounts of memory.
For additional information about loading tempdb in RAM, click the article number below to view the article in the Microsoft Knowledge Base:
INF: When to Use Tempdb in RAM
For additional information on considerations when configuring memory, click the article numbers below to view the articles in the Microsoft Knowledge Base:
INF: Memory Overhead for Connections, Objects, Locks, and DBs in SQL Server 6.x
Using DBCC BUFCOUNT to Configure HASH BUCKETS
4.20 sql6 sqlfaqtop perfmon machine machines sql65 configuration recommend recommendation