Why more (and varying number of) logical reads with read ahead (prefetch)?

Question

After creating tpch database in my SQL Server, I tried below query:

    set statistics io on
    DBCC DROPCLEANBUFFERS;        
    select top 100 * from dbo.lineitem order by l_partkey;

The table lineitem has a non-clustered index on l_partkey. I issued the above queries for several times and found out that the logical reads vary each time:

    Table 'lineitem'. Scan count 1, logical reads 1019, physical reads 4, read-ahead reads 1760, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'lineitem'. Scan count 1, logical reads 1007, physical reads 4, read-ahead reads 1720, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'lineitem'. Scan count 1, logical reads 1030, physical reads 4, read-ahead reads 1792, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

From the post here: Logical read count varies, I know it could be caused by read ahead behavior.

BUT exactly why read ahead could cause more logical reads? How does it change the SQL Server behavior? Like SQL Server may read more index page since it's in cache anyway?

Anyway, I disabled read ahead and issue the above query again. Now it reports the same amount of logical reads each time. BUT the logical reads is much smaller!!

    Table 'lineitem'. Scan count 1, logical reads 404, physical reads 160, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So my question is, why the read ahead feature could cause many more and various logical reads count?

Out of curiosity, I tried another query without the "order by":

    select top 100 * from dbo.lineitem

Here's the result without read ahead:

    Table 'lineitem'. Scan count 1, logical reads 5, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here's the result with read ahead:

    Table 'lineitem'. Scan count 1, logical reads 15, physical reads 2, read-ahead reads 3416, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The one with read ahead still has more logical reads. So, why?

Answer 1

The query plan for the ORDER BY l_partkey example almost certainly reads the nonclustered index in order (with read-ahead), followed by a Key Lookup to retrieve the uncovered columns.

The Nested Loops Join operator above the Lookup probably uses additional prefetching (WithOrderedPrefetch) for the Lookup. See the following article I wrote for full details:

• Nested Loops Prefetching

Also, as I mentioned in answer to the linked Q & A, the number of read-ahead reads depends on timing and storage subsystem characteristics. The same sort of considerations apply to Lookup prefetching at the Nested Loops Join.

Note that SQL Server issues read-ahead for pages that might be needed by the index scan, but this is not limited by the TOP specification in the query. The TOP is a query processor element, whereas the read ahead is controlled by the Storage Engine.

The activities are quite separate: read-ahead (and prefetching) issue asynchronous I/O for pages that may be needed by the Scan (or Lookup).

Depending on the order in which I/Os actually complete and make rows available to the query processor (among other things), the number of pages actually touched (logical reads) or physically read may vary. Note in particular that Lookup delayed prefetch also contributes to the logical reads when it checks to see if a page needed for the Lookup is already in memory or not.

So, it all comes down to the detailed timing of overlapping operations: The query processor will start to shut the query execution pipeline down as soon as the required number of rows (100) has been seen at the Top iterator. Quite how many asynchronous I/Os (read-ahead or prefetch) will have been issued or completed at that point is essentially non-deterministic.

You can disable Nested Loops Join prefetching with trace flag 8744 to explore this further. This will remove the WithOrderedPrefetch property from the Nested Loops Join. I usually use OPTION (QUERYTRACEON 8744) on the query itself. In any case, you need to be sure you're not reusing a cached plan that has the prefetch. Clear the plan cache each time or force a query recompilation with OPTION (RECOMPILE).

Logical reads is a simple measure of the number of cache pages touched on behalf of the query. Where read-ahead (and/or prefetching) is enabled, more (and different!) index and data pages may be touched in order to issue that read-ahead or as part of the prefetching activity.