Microsoft® SQL Server™ version 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.
Modern operating systems such as Microsoft Windows NT® and Microsoft Windows® 95/98 support virtual memory. Virtual memory is 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 that piece of memory is later referenced by an application, the operating system reads the memory page back from the pagefile into physical memory, also called swapping in memory. The total amount of memory available to applications is the amount of physical memory in the computer plus the size of the pagefile. If a computer has 256 MB of RAM and a 256 MB pagefile, the total memory available to applications is 512 MB.
One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations that happen 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 is, the less I/O SQL Server has to do to the database files. However, if the buffer cache causes SQL Server’s 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 tries to reach a balance between two goals:
There are differences in the way Windows NT and Windows 95/98 report virtual memory usage to applications. Because of this, SQL Server 7.0 uses different algorithms to manage memory on the two operating systems.
Testing has shown that Windows NT has minimal memory swapping until the memory allocations equal the available physical memory minus 4 MB. When running on Windows NT, the default is for SQL Server to attempt to keep free physical memory on the computer at 5 MB, plus or minus 200 KB. This amount of free space keeps Windows NT from paging excessively, while at the same time allowing SQL Server to have the largest buffer cache possible that will not cause extra swapping.
As other applications are started on the computer running SQL Server, they consume memory and the amount of free physical memory drops below 5 MB. SQL Server then frees memory from its address space. If another application is stopped and more memory becomes available, SQL Server increases the size of its memory allocation.
If SQL Server is running on a computer where other applications are frequently stopped or started, the allocation and deallocation of memory by SQL Server may slow the startup times of other applications. Also, if SQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL Server. In these cases, they can use the min server memory and max server memory options to control how much memory SQL Server can use. For more information, see Server Memory Options.
When running on Windows 95/98, SQL Server 7.0 uses a demand-driven algorithm for allocating memory. As more Transact-SQL statements are processed and demand for cached database pages rises, SQL Server requests more virtual memory. When the demands on SQL Server go down, such as when fewer Transact-SQL statements are being processed, SQL Server frees memory back to the operating system.