What is the reason the logical reads are different?
I am in the middle of doing some database tests. I am running the same code / procedure twice, since data is in cache the second run durations are shorter for most code. What I cannot understand is for only some tables when the code runs the second run the logical reads reported are quite a bit different. What is the reason for that?
The tables in question are on the inner side of a nested loops join that uses prefetching. The extra logical reads are associated with prefetch activity. When all the required data and index pages are in memory, no prefetching occurs, so no extra logical reads are generated.
See my recent blog post on the topic (http://bit.ly/Prefetch).