On the SQL Server performance stack one of the potential performance bottlenecks is the read-ahead rate at the Cache level.

 The relational engine manages the method of access  and the buffer manager. The methods of access relates to table scans &  index  scans . The buffer manager accesses and updates database pages.

 This is a very useful process. But the performance is dependant on the organisation of the data being prefetched. If the data is contigious – the Read Ahead manager can read large chunks of data at a single time.  On the other hand if the data is mixed i.e scattered across the extents and mixed in with other objects, then this places a greater cost on retrieving the data.

In those situations , aim to make the data as contigious as possible. Decrease fragmentation levels.

 Read-ahead is a database engine optimisation method. The theory is that the database engine will anticipate the requirement for data pages and index pages within a query, the manager will fetch the data and place it in the cache –in preparation for use.  

 While CPU is able to manage computational aspects of the query, the read ahead will read from disk. A read- ahead read will use a scatter – gather approach to placing in the buffer cache. Scatter – gather is ideal as it attempts to  deal with moving data in and out of non- contigious memory areas. If the particular page already exists in the buffer cache , it will be discarded. As mentioned above, it is ideal to decrease fragmentation levels.

 Read – ahead comes in two flavours : data page reads & index page reads

 Reading data pages involves building a list of disk addresses via the IAM.Reading index pages for non clustered indexes  uses  prefetching . As the leaf pages are being scanned – the database engine will begin to retrieve rows that have already been scanned.