Logical read count varies

Question

In this post the writer runs a query several times. I notice the logical reads vary a little across executions. There is a difference of about 2 pages in a total of a few thousand pages read. It seems clear to me from the context that there would be no write activity in between times. If the plan had changed I would have expected a larger variation than a fraction of a percent.

Q: what factors will cause SQL Server to report different logical read counts for the same query in the absence of data writes?

Answer 1

What factors will cause SQL Server to report different logical read counts for the same query in the absence of data writes?

For a heap table, there's no mechanism I know of that would cause this.

I notice that the author's reported logical reads only vary when compression was used. This suggests that the table rebuild was included on each execution. The rebuild statement does not specify MAXDOP = 1, so a parallel plan would have been generated. The way rows are distributed across threads in the rebuild at runtime is not deterministic (it depends on timing) so the final structure of the heap would vary. This is the only way I have been able to reproduce the published results.

For a clustered table (or a seek/scan of an index structure in general), the logical reads can vary due to read-ahead activity. Read-ahead reads pages from the upper levels of the b-tree to identify pages to read in advance of the scan, resulting in a varying number of 'extra' logical reads, depending on timing and the characteristics of the storage system. Disabling read-ahead with global trace flag 652 can be used to demonstrate this.

I mention the read-ahead aspect only for completeness, since the table in question is a heap. Heap scans do use read-ahead, but this is driven from the IAM pages. IAM pages do not form part of the main table structure (unlike the upper levels of a b-tree) so these extra reads are not reported by STATISTICS IO.