How to adjust memory usage by using configuration options in SQL Server

When I was at Tech Insights 2009, a few delegates asked me on ways that they could adjust memory usage by using configuration options in SQL Server. They were complaining that SQL Server uses a lot of memory. 🙂 In fact, a lot of people posted this question to me when I delivered trainings and talks. Well. I think is worth I blog on this.
 
When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB. This behavior alone does not indicate a memory leak. This behavior is normal and is an intended behavior of the SQL Server buffer pool. By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 – 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
 
You can establish upper and lower limits to the amount of memory (buffer pool) used by the SQL Server database engine with the min server memory and max server memory configuration options.
 
An Example:
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
 
Note:
 
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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: