4

On my query 1st time it was taking almost 39 seconds and showing me non-clustered index is missing and

Scan count 1, logical reads 14553, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0

After Creating the non-clustered index the duration of the query execution comes down to 1sec and it's showing

Scan count 2266, logical reads 4539, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0

Logical reads is decreased after creating the non-clustered index, but Scan count increased to 2266. So my question is the performance of the query is developed or the Scan count 2266 make my query non-optimal?

Improve this question
2
  • 39sec to read 14,553 pages from cache (114 MB) seems extremely slow. Perhaps you are also encountering blocking from concurrent activity.
    – Martin Smith
    Dec 12, 2015 at 10:37
  • It sounds to me like the data was being pulled out by a Hash Match, and is now being pulled out by an Inner Join. The construction of the hash table will be taking extra time in itself, even without the extra reads going on.
    – Rob Farley
    Dec 13, 2015 at 1:22

2 Answers 2

Reset to default
4

First scenario, it's scanning the whole table and looking through 14553 pages of data. Second scenario, it's doing 2266 seeks (but counted as range scans) which each look at just 2 pages. So the second one is way better. Plus, many of those seeks will probably be looking at pages which have just been looked at, so on a cold cache it will be an even larger performance benefit. And, the second is more likely to parallelise better, being lots of small operations rather than a large one (which could still be parallelised, but it's more effort).

Improve this answer
1

It can also be due to key lookups because the nonclustered index is not covering the query. Still it is better than the first one.

Improve this answer
1
  • 2
    probably not in the OP's case. It works out at 2 reads per seek plus seven left over. So this must be an index with a single root page and leaf level pages straight under. To be two reads per seek including bookmark lookups would require the whole index to fit on a single page (1st read) and physical rid lookup on a heap (second read). And it doesn't seem likely that the index could fit on a single page when the base table is 14,000 pages (though I guess it could occur in theory if the heap contains empty pages that haven't been deallocated)
    – Martin Smith
    Dec 12, 2015 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.