Introduction
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.
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.
You can use T-SQL to set your Max Server Memory
setting. The sample below sets it to 3500, which is the equivalent of 3.5GB.
This setting is dynamic in SQL Server 2005/2008, which means that you can change
it and it goes into effect immediately, without restarting SQL Server.
– Turn on advanced
options
EXEC
sp_configure‘Show Advanced Options',1;
GO
RECONFIGURE;
GO
– Set max server
memory = 3500MB for the server
EXEC
sp_configure‘max server memory (MB)',3500;
GO
RECONFIGURE;
GO
-- See what the current values are
EXEC sp_configure;