SQL Server Disk I/O

SQL Server 2000 relies on features provided by Windows NT/2000 to perform disk I/O. Windows NT/2000 can perform two typs of I/O: scatter-gather I/O and the more common, asynchronous I/O.

Asynchronous I/O

Asynchronous I/O is an I/O method that many operating systems support to provide better performance for concurrent processing environments. With asynchronous I/O, when a process submits a request for a read or write operation, the operating system immediately returns control back to the application while it goes off to perform the I/O. The application is free to perform additional work and check back periodically to see whether the read or write has completed.

In a synchronous I/O environment, the operating system doesn't return control to the application until the read or write completes, which results in unnecessary idle time and processes single threading operations through the I/O manager. SQL Server can perform multiple concurrent asynchronous I/O operations against each database file and will issue as many asynchronous, concurrent I/O requests as possible. The maximum number of I/O operations for any file is determined by the resources of the system.

Scatter-Gather I/O

Before the advent of scatter-gather I/O in Windows NT, all the data for a disk read or write had to be located in a contiguous area of memory. If a read request wanted to bring in 64KB of data, the read request had to locate and specify the address of a contiguous 64KB area of memory. Scatter-gather I/O is an I/O method supported by Windows 2000 and Windows NT 4 Service Pack 2 and later that allows a read or write to transfer data into or out of discontiguous areas of memory.

For example, if SQL Server 2000 were to read in a 64KB extent (8 pages * 8KB), it's not required to locate a single contiguous 64KB area to copy the individual pages into. It can choose to locate 8 separate buffer pages in SQL Server memory and then do a single scatter-gather I/O that reads the data into the 8 buffer pages. Windows NT/2000 places the 8 pages directly into the SQL Server buffer pages, eliminating the need for SQL Server to do a separate memory copy.

Read Ahead Reads

To minimize the number of I/O requests made when data needs to be brought into the buffer pool, SQL Server supports a mechanism called read ahead, whereby the need for data and index pages is anticipated, and pages are brought into the buffer pool before they're actually requested. Read ahead is managed completely internally and dynamically, and no configuration adjustments are necessary to fine-tune or enable it. In addition, read ahead doesn't use separate operating system threads. This ensures that read ahead stays far enough ahead of the scan of the actual data, without going too far ahead and reading too much data that is not going to be needed.

SQL Server performs two kinds of read ahead: one for table scans on heaps and one for scans on index ranges.

When performing table scans on a heap, SQL Server looks up the information stored in the table's allocation structures to read the table pages in disk order. SQL Server reads ahead up to a maximum of 32 extents (32 * 8 pages/extent * 8192 bytes/page = 2MB) at a time. If SQL Server is performing a scatter-gather read, only up to 4 extents (32 pages) at a time are read with a single 256KB scatter read. If the table is spread across multiple files in a file group, SQL Server establishes one read ahead thread per file. In SQL Server Standard Edition, each thread can still read up to 4 extents at a time from a file, and up to 32 files can be processed concurrently. In SQL Server Enterprise Edition, the number of pages it can read ahead is adjusted dynamically depending on the amount of available memory?SQL Server Enterprise Edition can read ahead enough data to fill up to 1 percent of the buffer pool.

When peforming index range searches, the read ahead reader looks at the level just above the leaf level of the index to identify which pages to read ahead. When the index scan starts, the read ahead reader searches the index for the search values, and it can tell from the level one index nodes how many pages have to be examined to satisfy the scan and how to best fetch them.

Merry-Go-Round Scans

SQL Server 2000 Enterprise Edition also includes another I/O optimization to improve the performance of nonordered table scans when multiple nonordered scans of the same table are requested simultaneously by different processes. When a process is scanning the table, if there isn't enough memory to cache the entire table, the pages at the beginning of the scan are flushed out of memory to make room for the additional rows. If another process is performing the same or a similar nonordered scan, it normally has to start at the beginning of the table again. With both processes reading from different parts of the table, and neither reading information the other can use, a lot of excessive disk I/O occurs.

Merry-Go-Round scans can avoid this problem by allowing the second process to start at the same point that the original process has already reached. Both processes can then read the same data with a single read of each page, which is used by both scans. When the first process finishes, the second process can then pick up from the beginning of the table and read the first portion of the data.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features