Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a query that has been working fine for ages (2+ years).

All of a sudden it went from taking 3 secs to 30+ to execute. The table indexes have minimal fragmentation, but rebuilding the indexes "fixes" it for about a day.

When I go to check the fragmentation it is around .5 (not bad). The table only sees about 100 inserts and another 100 updates a day.

There are no locks on the table when it happens. Any ideas of what to check?

share|improve this question
    
stats were updated at the same time i rebuilt the indexes –  Christopher Kelly Jul 14 '10 at 3:25

3 Answers 3

up vote 1 down vote accepted

It's probably parameter sniffing (StackOverflow search).

The index rebuild implicitly rebuilds index statistics which invalidates the cached plan which "fixes" it.

share|improve this answer

Check if your Statistics is Out-of-date and update it accordingly.

share|improve this answer
    
index stats are updated as part of an index rebuild. –  gbn Jul 14 '10 at 4:42

What does the execution plan look like when it is fine, vs when it isn't working correctly?

Are you rebuilding the indexes or defragging the indexes? How big is the table? When was the last time you updated stats on the table?

share|improve this answer
    
table has a grand total of 4 integer columns and ~20000 rows –  Christopher Kelly Jul 14 '10 at 3:26
    
+1 for execution plan –  Mark Henderson Jul 14 '10 at 4:55
    
How much data is changing on the table daily? It is really sounding like the stats are out of date. Can you post the execution plans? –  mrdenny Jul 18 '10 at 23:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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