Hi Friends,
SQL Server Read Ahead mechanism allows to anticipate the need and fetch a data or index pages into buffer pool before they are actually needed. This is completely managed internally and we do not require any configuration changes. This performance optimization allows large amounts of effective data processing.
There are two kinds of read-ahead to name them; one for table scans on heaps and other for index ranges. For the former i.e. table scans, tables allocation structure are checked to read data in disk order. 32 pages i.e. 4 extents are read at a time with a single 256 KB scatter read. In case table is spread across multiple files in a filegroup, SQL Server attempts to distribute read-ahead load evenly.
For the later, i.e. index ranges scan read-ahead uses level 1 index structure i.e. immediate level above the leaf to determine pages to read-ahead. When actual index scan starts read-ahead gets invoked on the initial descent of the index to minimize number of reads. Let’s try to understand this with an example. For a scan of CITY = ‘Seattle’, read-ahead searches for index for Key = ‘Seattle’ and can tell from level -1 nodes how many pages should be examined to satisfy the scan. If the pages are not contiguous they are fetched in scatter reads and if the number of pages are small, all pages are requested by initial read-ahead. Now, if the number of pages are high, initial read-ahead is performed and thereafter, each time another 16 pages are consumed by the scan, then index is consulted to refer another 16 pages. Read-ahead operation works both for clustered and non-clustered indexes. So, we can summarise as following;
- Whenever index is contiguous, small ranges can be processed in a single read at data page level.
- Scan range like in our case CITY = ‘Seattle’, can be used to prevent reading ahead of pages that won’t be used as this information is already available in the index.
- This operation doesn’t slow up in case of follow page linkages at data page level.
Reference
MSDN link to Read-Ahead : http://technet.microsoft.com/en-us/library/ms191475(v=sql.105).aspx
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
