Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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


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.