Why does an index scan read more pages than exist in an index?
My query is (a simple one):
and I have an exact covering nonclustered index for those columns.
In Profiler the number of pages read for this is 22,356.
But when looking at the index details, under Fragmentation, the number of pages for this index is 22,278.
What is the cause for this difference in pages read (78 to be precise)? Is the number in the Fragmentation section not reliable or is there some other explanation?
The index is not fragmented (or appears not to be), so there are no more pages than those 22,278 .. or are there? I wasn't able to find an explanation for this online, on Microsoft sites or otherwise.
(This is a controlled environment, no inserts and no deletes occurred on the table while taking screenshots for this question)
I was hoping in this case it would be easy to explain, but it wasn't without the additional work performed by the person who answered your question here.
Some of the places additional reads (or at least more reads than you might expect) can come from:
- tempdb usage (such as sorts that spill, worktable, workfile)
- read-ahead reads (as Hugo mentioned above)
- other work SQL Server has to do (such as reading various bits of data during plan or query_profile collection)
- non-leaf pages (which turned out to be the case here)
The first two can often be observed easily in other aspects of the plan (such as the Table I/O tab), but the latter takes additional digging. Perhaps the detailed physical_stats data is something that we could add (maybe to the Index Analysis tab?) in the future.