Why does an index scan read more pages than exist in an index?

Radu Gheorghiu 2018-01-12 13:43:24

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):

, 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)

Execution plan generated with Plan Explorer attached

Aaron Bertrand 2018-01-12 13:46:22
This plan was generated in SSMS and then opened in Plan Explorer. Can you please generate the actual plan from within Plan Explorer? This makes it easy to capture other I/O elements as well (which aren't available in the SSMS plan output).
Radu Gheorghiu 2018-01-12 14:08:16
I've generated the new plan with Plan Explorer and attached it. Thanks for the hint Aaron!
Hugo Kornelis 2018-01-12 19:27:58
Have you tried executing the query with SET STATISTICS IO ON? If so, what is the output?

(I have a very vague suspicion that read-ahead optimizing may be at play here, and if that's the case it might be visible from the SET STATISTICS IO results)

Also: Is the number of reads you see in profiler repeatabe? In other words, do you get the same number every time if you execute the query multiple times?

And finally, do you see a difference in Profiler when you do or do not enable the Include Actual Execution Plan option? (This is just me ticking off options, not a very realistic theory)

Radu Gheorghiu 2018-01-12 20:19:07
1. Yes, I did try it with SET STATISTICS IO ON. The output was always in line with what Profiler showed, so in this case it was 22,356.

  1. Yes, the number of reads is repeatable. The index or number of rows in the table or values in the table did not change while testing this. I was in full control of the environment.
  2. I did not see any differences with or without running the query with the Actual Execution Plan on. Actually, I got the same number of pages read when running the query from SQL Sentry Plan Explorer.
Aaron Bertrand 2018-01-12 20:08:30
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:

  1. tempdb usage (such as sorts that spill, worktable, workfile)
  2. read-ahead reads (as Hugo mentioned above)
  3. other work SQL Server has to do (such as reading various bits of data during plan or query_profile collection)
  4. 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.

Radu Gheorghiu 2018-01-15 08:50:17
Thank you very much for the additional details!