Read data pages

The I/O from an instance of the SQL Server Database Engine includes logical and physical

reads. A logical read occurs every time the Database Engine requests a page from the

buffer

cache

, also known as the

buffer pool. If the page isn’t currently in the buffer cache, a physical

read first copies the page from disk into the cache.

The read requests generated by an instance of the Database Engine are controlled by the

relational engine, and optimized by the storage engine. The relational engine determines the

most effective access method (such as a table scan, an index scan, or a keyed read). The access

methods and buffer manager components of the storage engine determine the general pattern

of reads to perform, and optimize the reads required to implement the access method. The

thread executing the batch schedules the reads.

The Database Engine supports a performance optimization mechanism called read-ahead.

Read-ahead anticipates the data and index pages needed to fulfill a query execution plan, and

brings the pages into the buffer cache before they’re used by the query. This process allows

computation and I/O to overlap, taking full advantage of both the CPU and the disk.

The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages

(512 KB) from one file. The read is performed as a single scatter-gather read to the appropriate

number of (probably noncontiguous) buffers in the buffer cache. If any of the pages in the

range are already present in the buffer cache, the corresponding page from the read is

discarded when the read completes. The range of pages might also be “trimmed” from either

end if the corresponding pages are already present in the cache.

There are two kinds of read-ahead: one for

data pages

and one for

index pages.

Table scans used by the Database Engine to read data pages are efficient. The index allocation

map (IAM) pages in a SQL Server database list the extents used by a table or index. The storage

engine can read the IAM to build a sorted list of the disk addresses that must be read. This

allows the storage engine to optimize its I/Os as large sequential reads that are performed in

sequence, based on their location on the disk. For more information about IAM pages, see

Manage space used by objects.