Dynamically Managing Memory on Windows NT and Windows 2000
When running on Microsoft® Windows NT® or Windows® 2000, the default memory management behavior of the SQL Server database engine is not to acquire a specific amount of memory, but to acquire as much memory as it can without generating excess paging I/O. The database engine does this by acquiring as much memory as is available, while leaving enough memory free to prevent the operating system from swapping memory.
When an instance of SQL Server starts, it typically acquires 8 to 12 MB of memory to complete the initialization process. After the instance has finished initializing, it acquires no more memory until users connect to it and start generating a workload. The instance then keeps acquiring memory as required to support the workload. As more users connect and run queries, SQL Server acquires the additional memory required to support the demand. The instance will keep acquiring memory until it reaches its memory allocation target, it will not free any memory until it reaches the lower limit of the target.
To acquire as much memory as possible without generating excess paging I/O, each instance of SQL Server sets a target of acquiring memory until free physical memory on the computer is in the range of 4 MB to 10 MB. This range was chosen because testing has shown that Windows NT and Windows 2000 have minimal memory swapping until the memory allocations equal the available physical memory minus 4 MB. An instance of SQL Server that is processing a heavy workload keeps the free physical memory at the lower end (4 MB) of the range; an instance that is processing a light workload keeps the free memory at the higher end of the range (10 MB).
An instance of SQL Server will vary its target as the workload changes. As more users connect and generate more work, the instance will tend to acquire more memory to keep the available free memory down at the 4 MB limit. As the workload lightens, the instance will adjust its target towards 10 MB of free space, and will free memory to the operating system. Keeping the amount of free space between 10 MB and 4 MB keeps Windows NT or Windows 2000 from paging excessively, while at the same time allowing SQL Server to have the largest buffer cache possible that will not cause extra swapping.
The target memory setting for an instance is related to the demand for pages in the database buffer pool relative to the size of the available pool. At any point in time, the overall demand for buffer pages is determined by the number of data pages required to satisfy all of the currently executing queries. If the demand for data pages is large relative to the number of pages in the buffer cache, then each page currently in the buffer is likely to be replaced by a new page in a relatively short time. This is measured by the page life expectancy performance counter of the Buffer Manager object. Having a high demand against a relatively small buffer generates a short life expectancy, the net effect is that I/O is increased because pages tend to be overwritten before they can be referenced by multiple logical reads. The database engine can alleviate this by acquiring more memory to increase the size of the buffer cache. The database engine will target free memory at the high end of the target (10 MB) when the page life expectancy is long, and at the low end of the target range (4 MB) when the page life expectancy is short.
As other applications are started on a computer running an instance of SQL Server, they consume memory and the amount of free physical memory drops below the SQL Server target. The instance of SQL Server then frees enough memory from its address space to raise the amount of free memory back to the SQL Server target. If another application is stopped and more memory becomes available, the instance of SQL Server increases the size of its memory allocation. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.