Why SQL Server memory is growing

SQL Server 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.