SQL Server memory is increasing, problem analysis

server| problem

SQL Server's memory is increasing

When the SQL Server database engine runs on microsoft®windows NT® or windows®2000, its default memory management behavior is not to get a specific amount of memory, but to get as much as possible without generating extra paging I/O. To do this, the database engine obtains as much free memory as possible while retaining enough available memory to prevent the operating system from swapping memory.

The SQL Server instance typically acquires 8 to MB of memory at startup to complete the initialization process. When the instance completes initialization, no more memory is obtained until the user connects to the instance and begins to generate the workload. At this point, the instance keeps getting memory to support the workload as needed. As more users connect and run queries, SQL Server gets the extra memory needed to support the requirements. The instance will continue to acquire memory until it reaches its own memory allocation target and will not release any memory until it reaches the lower bound of that target.

In order to get as much memory as possible without generating extra paging I/O, each instance of SQL Server sets a memory fetch target until the computer has a range of 4 MB to ten MB of usable physical. This range is selected because the test indicates that both Windows NT and Windows 2000 have a minimum memory exchange until the memory allocation equals the available physical memory minus 4 MB. The workload Processing task-heavy SQL Server instance retains the lower end of the range (4 MB) and the available physical memory for the light instance of the workload processing task retains the higher end of the range (MB).

The target of the SQL Server instance changes as the workload changes. When more users connect and produce more work, the instance tends to get more memory to keep the available memory below the 4 MB limit. When the workload is reduced, the instance adjusts its target to ten MB of free space and frees up memory for the operating system. Keeping the amount of free space between megabytes and 4 MB prevents Windows NT or Windows 2000 from excessive paging operations, while allowing SQL Server to obtain the largest possible cache memory without causing an additional exchange.

The target memory settings for an instance are related to the need for the database buffer pool's page relative to the size of the available pool. At any point in time, the total demand for the buffer page depends on the number of pages of data required to satisfy all currently executing queries. If the demand for a data page is large relative to the number of pages in the buffer cache, each page currently in the buffer is likely to be replaced by a new page in a relatively short time. This can be measured by the page Life performance counter of the Buffer Manager object. Situations where a relatively small buffer has a high demand will generate a short lifetime, and the sheer effect is to increase I/o because it is often rewritten before the page can be read by more than one logical reference. To mitigate this problem, the database engine can obtain more memory to increase the size of the cache memory. When the page lifetime is long, the database engine locates available memory at the target's high-end (MB), while the database engine is positioned at the low end of the target range (4 MB) when the page life span is short.

As other applications start on a computer that is running an instance of SQL Server, they consume memory, which causes the available physical amount to fall below the target of SQL Server. An instance of SQL Server frees up enough memory from its address space to return the available amount of RAM to the target of SQL Server. The SQL Server instance increases its memory allocation size if there are other applications that stop running and increase available memory. SQL Server can release and acquire a few megabytes of memory per second, which allows it to quickly adjust to memory allocation changes.

You can limit this by setting the maximum amount of memory that SQL Server can use:

Impact of minimum and maximum server memory
The min server memory and max server memory configuration options establish the upper and lower limits of the amount of memory used by the SQL Server database engine. The database engine does not immediately get the amount of memory specified in min server memory. The database engine starts with only the memory that is required to initialize. As the database engine workload increases, it continues to acquire the memory needed to support the workload. The database engine does not release any required memory until it reaches the specified amount of memory in min server memory. Once you reach min server memory, the database engine obtains and releases the required memory using a standard algorithm that keeps the operating system's available memory between 4 MB and MB. The only difference is that the database engine never drops the memory allocation to the level specified by min server memory, nor does it acquire more memory than the level specified by the max server memory.

The amount of memory that the database engine acquires depends entirely on the workload placed on the instance. Instances of SQL Server that do not process many requests may never reach the min Server memory.

If you specify the same value for the min server memory and max server memory, the database engine stops dynamically releasing and acquiring memory once the memory assigned to the database engine reaches that value.

If you frequently start or stop other applications on a computer running an instance of SQL Server, the time it takes to start these applications may be prolonged by the SQL Server instance allocating and freeing memory. In addition, if SQL Server is one of several server applications running on a single computer, the system administrator may need to control the amount of memory allocated to SQL Server. In these cases, you can use the min server memory and max server memory options to control the amount of memory that SQL Server can use.

How to set a fixed amount of memory (Enterprise Manager)
Set a fixed amount of memory

Expand a server group.

Right-click a server, and then clicking Properties.

Click the Memory tab.

Click Use fixed memory size (MB), and then place the fixed memory slider in the appropriate location.

Note If you use the default settings, Microsoft®sql Server™ dynamically configures the memory.

This is determined by the memory management mechanism of SQL Server