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

Article translations Article translations
Article ID: 321363 - View products that this article applies to.
This article was previously published under Q321363
Expand all | Collapse all

On This Page

Summary

When you start Microsoft 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 the Performance Monitor may show that the physical memory that is available on the computer steadily decreases until the available memory is between 4 MB and 10 MB.

This behavior alone does not indicate a memory leak. This behavior is typical 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 that the operating system reports. As long as sufficient memory (between 4 MB and 10 MB) is available to prevent paging, 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 obtain several megabytes of memory each second. This allows for SQL Server to quickly adjust to memory allocation changes.

More information

You can establish upper and lower limits for how much memory (buffer pool) is used by the SQL Server database engine with the min server memory and max server memory configuration options. Before you set the min server memory and max server memory options, review the references in the section titled "Memory" in the following Microsoft Knowledge Base article:
319942 How to determine proper SQL Server configuration settings
Notice that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the previous allocations, it is typical for the SQL Server private bytes to exceed the max server memory configuration. For more information about allocations from this unreserved memory area, click the following article number to view the article in the Microsoft Knowledge Base:
316749 There may not be enough virtual memory when you have a large number of databases in SQL Server

REFERENCES

Refer to SQL Server Books Online and topics such as "Effects of min and max server memory," "Memory Architecture," "Server Memory Options," and "SQL Server Memory Pool."

For more information about the min server memory configuration option and the max server memory configuration option in SQL Server 2005, see the "Effects of min and max server memory" topic in SQL Server 2005 Books Online.

Properties

Article ID: 321363 - Last Review: July 12, 2013 - Revision: 7.1
Applies to
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbsqlsetup kbinfo KB321363

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com