HAPPY NEW YEAR TO ALL

SQL Server supports the read ahead mechanism. The data and index pages can be pre-fetched on anticipation and the pages can be bought into the buffer pool ahead of their required read requirements. Read-Ahead is internal to SQL Server and is not available as a configuration change.

There are 2 kinds of read-ahead’s, one for table scans on heaps and one for index ranges.

Before we proceed, simple things to remember about page and extents are as follows:

1 Page = 8KB
8Pages = 64KB
8Pages = 1 extent (64 KB)
16 extents (64 X 16) = 1 MB(1024 KB)
1 MB = 128 Pages or 16 extents

up to 2MB of read-ahead may be available at any time.
[32 extents (32 * 8 pages / extent * 8192 bytes/page)=2 MB]

6 extents [64 contiguous pages (1 extent= 8 pages)] at a time are read with single 512KB Read from HDD to buffer cache.

For Index, the scan will use the level one (above leaf level) of the index structure, to determine which pages to read BEFORE hand. So the read-ahead mechanism starts to descent at the first initialization of index read.