I've been trying out running some queries for a testing scenario and I noticed that when doing a full-scan of a nonclustered index, SQL Server reads more pages than actually are in the index.
My query is (a simple one):
select sometext , somemoretext from tbl
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)
By Radu Gheorghiu 1 asked Jan 12 at 01:43 PM
Additional reads (both in trace, which is what feeds Profiler and the reads column in Plan Explorer's statement grid, and Statistics IO, which is what feeds the Table I/O tab in PE) can come from a variety of places.
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:
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.
By Aaron Bertrand ♦ 1.7k answered Jan 12 at 08:08 PM