Buffer Management and I/O

SQL Server 7.0 uses a clocking mechanism to manage buffer I/O instead of a least recently used list. The clocking mechanism improves performance because there is less synchronization required. This improves scaling on large SMP systems, but does not have much effect on small systems. Query performance is improved because it makes use of both parallel I/O and large I/O on the files that comprise the database object.

The SQL Server: Buffer Manager object provides counters to monitor how SQL Server uses memory to store data pages, internal data structures, the procedure cache, and physical I/O as SQL Server reads database pages from and writes database pages to disk.

Monitoring the memory used by SQL Server can help determine, for example, whether bottlenecks exist due to a lack of available physical memory for storing frequently accessed data in cache, in which case SQL Server must retrieve the data from disk. By monitoring memory, you can determine if query performance can be improved by adding more memory or by making more memory available to the data cache or SQL Server internal structures. Monitoring the physical I/O is especially important to determine how often SQL Server needs to read data from the disk.

Compared to other operations, such as memory access, physical I/O consumes a lot of time. Minimizing physical I/O can improve query performance.