1

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

Mucida
  • 603
  • 9
  • 24
  • I am not sure 62ms is particularly consequential, nor is 290 reads. In this case I think the difference in time could be down to any number of factors. How many times did you run the comparison? Is this a fair test with no other queries being run simultaneously? Is 397ms total running time really long enough to require looking into the optimisation? – GarethD Dec 01 '14 at 18:21
  • @GarethD I ran it like 20 times and the option with more logical reads was faster in all of them. I agree that my table is not that big and slow to try to optimize it, but when I saw more reads but less elapsed time, I started to think how critical is the amount of logical reads in a query. As I sad, I thought that no matter what, if I have more pages to read, my query takes longer to run – Mucida Dec 01 '14 at 18:34
  • Maybe the pages have different sizes with or without index... – Mucida Dec 01 '14 at 18:42
  • Pages are always 8KB – brian Dec 01 '14 at 20:35

1 Answers1

0

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.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33