SQL Server Read Ahead Mechanism

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 TwitterFollow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.