Server Memory Options

Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in MB) in the buffer pool (not the amount of memory used by the entire process) that Microsoft® SQL Server™ uses.

By default, SQL Server can change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647.

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory available. SQL Server grows or shrinks the buffer cache to keep free physical memory at 5 MB plus or minus
200 KB to prevent Microsoft Windows NT® from paging. If there is less than 5 MB free, SQL Server releases memory to Windows NT that usually goes on the free list. If there is more than 5 MB of physical memory, SQL Server recommits memory to the buffer cache. SQL Server adds memory to the buffer cache only when its work load requires more memory; a server at rest does not grow its buffer cache.

Allowing SQL Server to use memory dynamically is the recommended configuration; however, you can set the memory options manually and override SQL Server’s ability to use memory dynamically. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting the memory required for Windows NT (and other system uses, if the computer is not wholly dedicated to SQL Server) from the total physical memory. This is the maximum amount of memory you can assign to SQL Server.

Note If you have installed and are running the Full-Text Search support (Microsoft Search service, also known as MSSearch), then you must set the max server memory option manually to leave enough memory for the MSSearch service to run. The max server memory setting must be adjusted in conjunction with the Windows NT virtual memory size such that the virtual memory remaining for Full-Text Search is 1.5 times the physical memory (excluding the virtual memory requirements of the other services on the computer). Configure the SQL Server max server memory option so that there is sufficient virtual memory left to satisfy this Full-Text Search memory requirement:

total virtual memory - (SQL Server maximum virtual memory + virtual memory requirements of other services) >= 1.5 times the physical memory

Setting the Memory Options Manually

There are two principal methods of setting the SQL Server memory options manually.

In the first method, set min server memory and max server memory to the same value. That value corresponds to the fixed amount of memory to allocate to SQL Server.

In the second method, set min server memory and max server memory to span a range of memory values. This is useful in situations where system or database administrators want to configure SQL Server in conjunction with the memory requirements of other applications running on the same computer.

Use min server memory to guarantee a minimum amount of memory to SQL Server. Use max server memory to prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. SQL Server gives memory back to the operating system if another application starts and there is less than 5 MB of memory free. There is a short delay between the start of a new application and the time SQL Server releases memory. Using max server memory will avoid this delay and may give better performance to the other application. Only set min server memory if the start time of other applications sharing the same server as SQL Server shows up as a problem. It is better to let SQL Server use all of the available memory.

The minimum amount of memory you can specify for max server memory is 4 MB.

If you set the memory options manually, be sure to set them appropriately for servers used in replication. If the server is a remote Distributor or a combined Publisher/Distributor, you must assign it at least 16 MB of memory.

Ideally, you want to allocate as much memory as possible to SQL Server without causing the system to swap pages to disk.. The threshold varies depending on your system. For example, on a 32-MB system, 16 MB might be appropriate for SQL Server; on a 64-MB system, 48 MB might be appropriate.

Note As you increase the amount of SQL Server memory, ensure that there is sufficient disk space to grow the operating system’s virtual memory support file (Pagefile.sys) to accommodate additional memory. For information about the virtual memory support file, see your Windows NT documentation.

The amount of memory specified must be sufficient for the SQL Server static memory needs (kernel overhead, open objects, locks, and so on), as well as for the data cache (also called buffer cache).

Use statistics from Windows NT Performance Monitor to help you adjust the memory value if necessary. Change this value only when you add or remove memory, or when you change how you use your system.

Virtual Memory Manager

Windows NT provides a 4-GB virtual address space at any time, the lower 2 GB of which is private per process and available for application use. The upper 2 GB is reserved for system use. Windows NT Enterprise Edition provides a 4-GB virtual address space for each Microsoft Win32® application, the lower 3 GB of which is private per process and available for application use. The upper 1 GB is reserved for system use.

The 4-GB address space is mapped to the available physical memory by 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 perceives only 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 control memory residency directly.

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, using significantly more virtual memory than the combined average working sets of all the processes results in poor performance.

For better performance, SQL Server can lock memory as a working set. Because memory is locked, you can receive out of memory errors when running other applications. If out of memory errors occur, you may have too much memory assigned to SQL Server. The set working set size option (set with sp_configure or SQL Server Enterprise Manager) can disable the locking of memory as a working set. By default, set working set size is disabled.

Configuring SQL Server manually 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 overhead). System overhead requirements can grow as SQL Server parameters are configured upward and Windows NT needs more resident memory to support additional threads, page tables, and so on. Allowing SQL Server to use memory dynamically helps to avoid memory-related performance problems.

min server memory and max server memory are advanced options. If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. These settings take effect immediately (without a server stop and restart).

To set a fixed amount of memory

See Also