The Microsoft SQL Server 7.0 Read Ahead Manager is completely self-configuring and self-tuning. Read Ahead Manager is tightly integrated with the operations of SQL Server query processor. SQL Server query processor identifies and communicates situations that will benefit from read-ahead scans to the Read Ahead Manager. Large table scans, large index range scans, and probes into clustered and nonclustered index B-trees are situations that would benefit from a read-ahead. Read-ahead reads occur with 64-KB I/Os, which provide higher disk throughput potential for the disk subsystem than 8-KB I/Os do. When a large amount of data must be retrieved from SQL Server, read-ahead is the best way to do it.
Read Ahead Manager benefits from the simpler and more efficient Index Allocation Map (IAM) storage structure. The IAM is the SQL Server 7.0 method of recording the location of extents (eight pages of SQL Server data or index information for a total of 64 KB of information per extent). The IAM is an 8-KB page that tightly packs information through a bitmap about which extents within the range of extents covered by the IAM contain required data. The compact IAM pages are fast to read and tend to keep regularly used IAM pages in buffer cache.
The Read Ahead Manager can construct multiple sequential read requests by combining the query information from query processor and quickly retrieving the location of all extents that must be read from the IAM page(s). Sequential 64-KB disk reads provide excellent disk I/O performance.
Read-ahead activity is monitored by the SQL Server: Buffer Manager - Readahead Pages counter. You can find more information about read-ahead activity by executing the DBCC PERFMON (IOSTATS) statement. Some of the information provided is RA Pages Found in Cache and RA Pages Placed in Cache. If the page is already hashed (the application read it in first and read-ahead wasted a read), it is a page found in cache. If the page is not already hashed (a successful read-ahead), it is a page placed in cache.
Too much read-ahead can be detrimental to overall performance because it can fill cache with unnecessary pages, requiring additional I/O and CPU that could have been used for other purposes. The solution is a performance tuning goal that all Transact-SQL queries are tuned so a minimal number of pages are brought into buffer cache. This includes using the right index for the right job. Save clustered indexes for efficient range scans and define nonclustered indexes to help locate single rows or smaller rowsets quickly.
For more information, see SQL Server Books Online.