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

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)

Execution plan generated with Plan Explorer attached

avatar image By Radu Gheorghiu 1 asked Jan 12 at 01:43 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jan 12 at 01:46 PM

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

avatar image Radu Gheorghiu Jan 12 at 02:08 PM

I've generated the new plan with Plan Explorer and attached it. Thanks for the hint Aaron!

avatar image Hugo Kornelis Jan 12 at 07:27 PM

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)

avatar image Radu Gheorghiu Jan 12 at 08:19 PM
  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.

  2. 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.

  3. 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.

10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Aaron Bertrand ♦ 1.7k answered Jan 12 at 08:08 PM
more ▼
(comments are locked)
avatar image Radu Gheorghiu Jan 15 at 08:50 AM

Thank you very much for the additional details!

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Jan 12 at 01:43 PM

Seen: 107 times

Last Updated: Jan 15 at 08:50 AM