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 16 megabytes (MB).
Note
Setting max server memory to the minimum value can severely reduce SQL Server performance and even prevent it from starting. If you cannot start SQL Server after changing this option, start it using the –f startup option and reset max server memory to its previous value. For more information, see Using the SQL Server Service Startup Options.
When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory. Under Microsoft Windows 2000, SQL Server grows or shrinks the buffer cache to keep free physical memory between 4 MB and 10 MB depending on server activity. Maintaining this free memory prevents Windows 2000 from paging. If there is less memory free, SQL Server releases memory to Windows 2000. If there is more memory free, SQL Server allocates memory to the buffer pool. SQL Server adds memory to the buffer pool only when its workload requires more memory; a server at rest does not increase the size of its buffer pool.
Under Windows Server 2003, SQL Server uses the memory notification API QueryMemoryResourceNotification to determine when the buffer pool may allocate memory and release memory.
Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. 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 2000 or Windows Server 2003 and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server.
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 the SQL Server buffer pool after the value is reached.
In the second method, set min server memory and max server memory to span a range of memory values. This method is useful where system or database administrators want to configure an instance of SQL Server in conjunction with the memory requirements of other applications that run on the same computer.
Use min server memory to guarantee a minimum amount of memory available to the buffer pool of 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 requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.
Use max server memory to prevent the SQL Server buffer pool 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.
Before reducing the max server memory value, use Performance Monitor to examine the SQLServer:Buffer Manager performance object while under a load, and note the current values of the Stolen pages and Reserved pages counters. These counters report memory as the number of 8K pages. max server memory should be set above the sum of these two values to avoid out-of-memory errors. An approximate value for the lowest reasonable max server memory setting (in MB) is ([Stolen pages] + [Reserved pages])/ 100. To reduce the max server memory you may need to restart SQL Server to release the memory. For information about how to set memory options, see How to: Set a Fixed Amount of Memory (SQL Server Management Studio).
Maximize Data Throughput for Network Applications
To optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected. You can specify the smallest file system cache by selecting Minimize memory used or Balance.
To check the current setting on your operating system
Click Start, then click Control Panel, double-click Network Connections, and then double-click Local Area Connection.
On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.
If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.
AWE Memory Under Windows Server 2003
Under Windows Server 2003, SQL Server can use Address Windowing Extensions (AWE) memory to further assist in load balancing its own memory requirements with those of the operating system. This balancing between SQL Server and the operating system is subject to the constraints of the min server memory and max server memory options. If the server hardware supports Hot-Add Memory, additional physical memory can be added to the server as necessary without requiring a restart. For more information about the awe enabled configuration option, see awe enabled Option. For more information, see Hot Add Memory.
Note
Under Windows Server 2003, dynamic AWE memory is supported on servers with less physical memory than the configured virtual memory limit. Windows 2000 required that the available physical memory exceed the virtual memory in order to enable AWE memory.
Ideally, 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-gigabyte (GB) system exclusively dedicated to SQL Server, 30-31 GB might be an appropriate maximum threshold for SQL Server; on a 64-GB system, 60-62 GB might be an appropriate threshold.
Note
As you increase the amount of SQL Server memory, ensure that there is sufficient disk space to expand 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 2000 or Windows Server 2003 documentation.
Use statistics from the Windows System 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
The 32-bit operating systems such as Windows 2000 and Windows Server 2003 provide access to 4-gigabyte (GB) of virtual address space. The lower 2 GB of virtual memory is private per process and available for application use. The upper 2 GB is reserved for operating system use. All operating system editions, starting with Microsoft Windows XP Professional and later, including Windows Server 2003, include a boot.ini switch that can provide applications with access to 3 GB of virtual memory, limiting the operating system to 1 GB. For more information about how to use the /3GB switch memory configuration, see the Windows documentation.
Note
In Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices. In this case, you do not have to use the /PAE switch on a system that is configured to use hot-add memory devices. In all other cases, you must use the /PAE switch in the Boot.ini file to take advantage of memory over 4GB.
The 4-GB address space is mapped to the available physical memory by the Windows Virtual Memory Manager (VMM). The amount of physical memory supported has increased with the introduction of the Windows Server 2003 operating systems. The physical memory accessible by AWE therefore depends on which operating system you are using:
Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
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.
Windows 2000 Advanced Server and Windows 2000 Datacenter Server as well as Windows Server 2003, Enterprise Server and Windows Server 2003, Datacenter Server 32-bit editions have the capability to exceed the 4 GB limit with the use of Intel Physical Addressing Extensions (PAE). Use of the /PAE switch in the Windows boot.ini configuration file provides access to physical memory above the 4 GB limit. This is a requirement for AWE memory access above 4 GB. For more information about how to use this memory configuration for Windows 2000 and Windows Server 2003, see the Windows documentation.
Virtual address systems such as Windows 2000 or Windows Server 2003 allow the over-commitment of physical memory, so that the ratio of virtual to physical memory can exceed 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 can cause poor performance.
The min server memory and max server memory options 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 restart.
Running Multiple Instances of SQL Server
When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory:
Use max server memory to control memory usage. Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. You might want to give each instance memory proportional to its expected workload or database size. This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.
Use min server memory to control memory usage. Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Again, you may establish these minimums proportionately to the expected load of that instance. This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL Server would at least get a reasonable amount of memory. The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so. You may also need to increase the size of your paging file significantly.
Do nothing (not recommended). The first instances presented with a workload will tend to allocate all of memory. Idle instances or instances started later may end up running with only a minimal amount of memory available. SQL Server makes no attempt to balance memory usage across instances. All instances will, however, respond to Windows Memory Notification signals to adjust the size of their buffer pools. As of Windows Server 2003 SP1, Windows does not balance memory across applications with the Memory Notification API. It merely provides global feedback as to the availability of memory on the system.
You can change these settings without restarting the instances, so you can easily experiment to find the best settings for your usage pattern.
Examples
The following example sets the max server memory option to 4 GB:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO
See Also