Memory Management

Microsoft SQL Server 7.0 dynamically acquires and frees memory as needed. It is no longer necessary for an administrator to specify how much memory should be allocated to SQL Server, although the option still exists.

There are several competing subsystems in SQL Server that must share available memory. The log and recovery systems need memory to read and undo pages, and the query processor needs memory to perform hashing and sorting. Other subsystems that use memory are the procedure cache, buffer pool, lock manager, and data structures. In SQL Server 7.0, these systems allocate the memory they need dynamically and can return memory when it is no longer needed.

Microsoft Windows NT, Microsoft Windows 95, and Microsoft Windows 98 operating systems support virtual memory, a method of extending the available physical memory on a computer. In a virtual memory system, the operating system creates a pagefile, or swapfile, and divides memory into units called pages. Recently referenced pages are located in physical memory, or RAM. If a page of memory is not referenced for a while, it is written, or swapped out, to the pagefile. If the page is later referenced by an application, it is read, or swapped into physical memory, from the pagefile. The total memory available to applications is the amount of RAM plus the size of the pagefile.

One of the primary design goals of database software is minimizing disk I/O, because disk reads and writes are among the most resource-intensive operations on a computer. SQL Server builds a buffer cache in memory to hold pages read from the database. Much of the code in SQL Server is dedicated to minimizing the number of physical reads and writes between the disk and the buffer cache. The larger the buffer cache, the less I/O SQL Server has to do. However, if the buffer cache causes the SQL Server memory requirements to exceed the available physical memory on the server, then the operating system starts swapping memory to and from the pagefile. All that has happened is that the physical I/O to the database files has been traded for physical I/O to the swap file.

Having a lot of physical I/O to the database files is an inherent factor of database software. By default, SQL Server attempts to balance between two goals:

SQL Server starts with a default memory value. As more applications are started, the system is queried periodically to determine the amount of free physical memory available. SQL Server grows or shrinks the buffer cache to keep free physical memory at around 5 MB, which also prevents Windows NT from paging. If there is less than 5 MB of free memory, then SQL Server releases memory to Windows NT that usually goes on the free list. If there is more than 5 MB of free memory, SQL Server recommits memory to the buffer cache. SQL Server adds memory to the buffer cache when its work load requires more memory; a server at rest does not grow its buffer cache.