Why my SQL query is reading more pages but is faster?

Question

I was testing some nonClustered indexes to be added in a table inside a view (that has 7 inner joins). After running Tunning Advisor (SQL Server 2008) it showed me a script to create a nonClsutered index in a table (a) that could help me to optimize the query.

Before creating the index, I ran the query and got the IO and TIME Statistics:

a) **Scan count 2, logical reads 420**
b) Scan count 2, logical reads 6
c) **Scan count 2, logical reads 40**
d) Scan count 3, logical reads 12
e) Scan count 4, logical reads 28
f) Scan count 4, logical reads 16
g) Scan count 2, logical reads 4
h) Scan count 1, logical reads 3
   CPU time = 172 ms,  elapsed time = 397 ms.

After creating the nonClustered index I got this:

a) **Scan count 16, logical reads 710**
b) Scan count 2, logical reads 6
c) **Scan count 2, logical reads 8**
d) Scan count 3, logical reads 12
e) Scan count 4, logical reads 28
f) Scan count 4, logical reads 16
g) Scan count 2, logical reads 4
h) Scan count 1, logical reads 3
   CPU time = 187 ms,  elapsed time = 335 ms.

Check the lines A and C, I have almost 300 more pages being read in A and just 32 less in B. So why is this query faster? I always thought that the more pages the query reads, the worse it performs

Answer 1

The short answer would be "because these 2 queries use different execution plans". Why exactly more reads resulted in faster execution in your case - that's impossible to answer without having both execution plans (and I don't mean screenshots from SSMS, of course) no matter how hard we all will polish our crystal balls.

If you are into that sort of thing, you have to read, first, a lot of documentation about how to read execution plans, adding after that a couple of books written by people like Kalen Delaney and Itzik Ben-Gan and others of that calibre that frequently go to extreme lengths in explaining lowest level intricacies of this and that.

Or you can try to attach plans in textual form to your question and hope that 1) someone will be able to point out the actual cause of observed behaviour and, 2) you will be able to understand the explanation.