Server Memory Options
Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) in the buffer pool used by an instance of Microsoft® SQL Server™.
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. The minimum amount of memory you can specify for max server memory is 4 megabytes (MB).
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 between 4 MB and 10 MB depending on server activity. This prevents Microsoft Windows NT® 4.0 or Windows® 2000 from paging. If there is less memory free, SQL Server releases memory to Windows NT 4.0 or Windows 2000 that usually goes on the free list. If there is more memory free, SQL Server recommits memory to the buffer cache. SQL Server adds memory to the buffer cache only when its workload 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 from the total physical memory the memory required for Windows NT 4.0 or Windows 2000 and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). 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 4.0 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 for setting the SQL Server memory options manually:
- In the first method, set min server memory and max server memory to the same value. This 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 an instance of 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 an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory from the allocated buffer pool unless the value of min server memory is reduced.
Note SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never necessitates the allocation of the amount of memory specified in min server memory, then SQL Server will run with less memory.
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 does not immediately allocate the memory specified in max server memory on startup. Memory usage is increased as needed by SQL Server until reaching the value specified in max server memory. SQL Server cannot exceed this memory usage unless the value of max server memory is raised.
Important Instances of SQL Server 2000 running in Address Windowing Extensions (AWE) memory mode do allocate all the full amount of memory specified in max server memory on server startup. For more information about AWE memory, see Managing AWE Memory.
There is a short delay between the start of a new application and the time SQL Server releases memory. Using max server memory prevents this delay and may give better performance to the other application. Only set min server memory if the start time of new 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.
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 more information about the virtual memory support file, see the Windows NT 4.0 and Windows 2000 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 System Monitor (Performance Monitor in Windows NT 4.0) 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 4.0 and Windows 2000 provide a 4-gigabyte (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 Server, 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 4.0 or Windows 2000 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.
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 4.0 or Windows 2000 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 4.0 or Windows 2000 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