Memory Architecture
Microsoft® SQL Server™ 2000 dynamically acquires and frees memory as needed. It is typically not necessary for an administrator to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments. When running multiple instances of SQL Server on a computer, each instance can dynamically acquire and free memory to adjust for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for using Microsoft Windows® 2000 Address Windowing Extensions (AWE) to address approximately 8GB of memory for instances running on Windows 2000 Advanced Server, and approximately 64GB for instances running on Windows 2000 Data Center. Each instance using this extended memory, however, must statically allocate the memory it needs.
Virtual Memory and the Database Engine
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 to the pagefile. This is called swapping or paging out memory. 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 or paging 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. Operating systems such as Microsoft Windows NT®, Windows 2000, Windows 95, and Windows 98 support virtual memory.
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. 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 memory requirements to exceed the available physical memory on the server, 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:
- Minimizing or eliminating pagefile I/O to concentrate I/O resources for reads and writes of the database files.
- Minimizing physical I/O to the database files by maximizing the size of the buffer cache.
By default, the SQL Server 2000 editions dynamically manage the size of the address space for each instance. There are differences in the way Windows NT, Windows 2000, Windows 95, and Windows 98 report virtual memory usage to applications. Because of this, SQL Server 2000 uses different algorithms to manage memory on these operating systems.
SQL Server 2000 Enterprise Edition does not default to dynamic memory management if you are using Windows 2000 AWE to support large address spaces.