Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

↑ Back to the top


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.

↑ Back to the top


Keywords: kbsqlsetup, kbyukonsweep, kbappliestoyukon, kbinfo, kbbillprodsweep, kbsmbportal, kb

↑ Back to the top

Article Info
Article ID : 321363
Revision : 1
Created on : 1/7/2017
Published on : 7/12/2013
Exists online : False
Views : 345