This topic discusses the amount of memory that should be allocated to SQL Server in various machine memory configurations.
Microsoft Windows NT provides each Win32 application a 4-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 Win32 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 virtual 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.
For better performance, SQL Server "locks" memory and tempdb (if using tempdb in ram) as a working set. Because of this, it is possible for you to receive "out of memory" errors when running other applications. The set working set size parameter (set with sp_configure or SQL Enterprise Manager) can disable the "locking" of memory as a working set. If "out of memory" errors occur, you may have too much memory assigned to SQL Server.
Configuring SQL Server for more virtual memory than there is physical memory 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). This system overhead requirement can grow as SQL Server parameters are configured upward and Windows NT needs more resident memory to support such things as additional threads, page tables, and so on.
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 figure of this and assumes a dedicated database server. If the machine is shared between multiple uses, such as a file server, a database server, and a client workstation, less memory should be given to SQL Server and more left for the operating system and other uses.
| Machine memory (MB) |
Approximate SQL Server memory allocation (MB) |
|---|---|
| 16 | 4 |
| 24 | 8 |
| 32 | 16 |
| 48 | 28 |
| 64 | 40 |
| 128 | 100 |
| 256 | 216 |
| 512 | 464 |
Note These 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 SQL Performance Monitor to determine your system memory behavior. Another good source of information is Optimizing Windows NT, from the Windows NT Resource Kit, which devotes nearly 600 pages to various aspects of monitoring and optimizing Windows NT and Win32 applications.
16 MB is the minimum available memory for SQL Server on Intel-based computers. SQL Server on Alpha AXP- and MIPS-based computers will require more memory because of the average lower density of RISC machine 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 small as a percentage of total memory rarely afford any significant benefit. Two things control this point:
For this reason, whether 14 MB, 16 MB, or 18 MB is given to SQL Server on a 32 MB computer 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 adding memory will be beneficial should be studied beforehand. The easiest way to determine this is by using SQL 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%), adding more memory will usually 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.
For more information about SQL Performance Monitor, see Chapter 19, Monitoring Server Activity and Performance.