-2

I have two notebooks with same specs (same model, only ram is 24GB/32GB, same ssd) and exact same database and same sql server version. SQLIO is 3300 and 3600.

Now I run a query on one notebook and get ~100 reads in the sql server profile and on the second notebook I get ~40000 reads for the same query.

I assume that the index is not used on the second notebook, even tho its there because its the same database (I restored it). The index usage report shows that the index is not used for the same query. The duration for the query therefore is ~100x higher.

Notebook 1, index used Notebook 2, no index usage

How can I fix that?

Update1 4/16/2014, 4:35pm:

When I re-execute one of my 35.000 queries, the query plan shows me that the index is used. Still, I don't understand why the reads are 100x higher, this has of course, a performance impact.

execution plan

Improve this question
  • What's the execution plan? Have you tried clearing the plan cache? – Mark Sinkinson Apr 16 '14 at 13:12
  • The execution plan is the same - still the count of reads is massively different. Any ideas why? – Max Melcher Apr 16 '14 at 13:31
  • Index usage stats are reset when the server is restarted, and includes all activity on the server. It's possible there was some other usage of these indexes besides the query you ran. Soooo many variables here... – Jon Seigel Apr 16 '14 at 13:36
  • 2
    How can the execution plan be the same if one uses a certain index and the other doesn't? – Mark Sinkinson Apr 16 '14 at 13:48
  • 1
    Why are you guessing and not looking at the execution plan? You'll be able to see pretty quickly if it is/is not using the index... – Mark Sinkinson Apr 16 '14 at 14:25
1

Your arrow points to the #user seeks column for the clustered index in the report. The execution plan says it is doing a scan on the non clustered index. Are you expecting it to do a seek on the clustered?

Improve this answer
1

So the problem was sitting in front of the PC - once again.

It turned out that the query was not exactly similar, there was one where condition more in every query. We had different entity framework versions on the two notebooks that created different statements.

Improve this answer

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