The read requests generated by the system are controlled by the relational engine and are optimized further by the storage engine. The access method used to read pages from a table determines the general pattern of reads performed. The relational engine determines the most effective access method, such as a table scan, an index scan, or a keyed read. This request is then given to the storage engine, which optimizes the reads required to implement the access method. The reads are requested by the thread executing the batch.
The SQL Server 7.0 read-ahead mechanism is improved and significantly simplified. By putting hints and directions in both the query processor and the storage engine, more than 1,000 lines of code were eliminated, as well as configuration parameters.
Table scans benefit from the new data structures introduced in SQL Server 7.0. In a SQL Server 7.0 database, the storage engine can build a serial list of the disk addresses that must be read. This allows SQL Server to optimize its I/O operations as large sequential reads in disk order. SQL Server issues multiple serial read-ahead reads immediately for each file in the scan. This takes advantage of striped disk sets.
The parallel read-ahead mechanism allows the disk subsystem to work at maximum speed. The inputs and outputs are decoupled, and the mechanism pulls in data pages and then passes them to the next available CPU. I/O operations are simultaneously issued against multiple files.
SQL Server 7.0 eliminates separate read-ahead threads and minimizes context switches. The new allocation data structures allow read ahead without following a page chain. The query processor helps with read ahead by using the middle tier of an index to predict the next page of an index scan (including clustered table scans).
SQL Server 7.0 also reads index pages serially in disk order, thereby improving the performance of index scans. Index processing is further improved by the use of prefetch hints to allow serial read-ahead processing of a nonclustered index.