Mysterious timeouts in MSSQL 2000

Question

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?

stats were updated at the same time i rebuilt the indexes – KellCOMnet Jul 14 '10 at 03:25 — KellCOMnet, Jul 14 '10 at 03:25

score 1 · Answer 1 · answered Jul 13 '10 at 21:31

1

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

answered Jul 13 '10 at 21:31

DaniSQL

1,107
7
12

index stats are updated as part of an index rebuild. – gbn Jul 14 '10 at 04:42

score 1 · Answer 2 · answered Jul 13 '10 at 22:23

1

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?

answered Jul 13 '10 at 22:23

mrdenny

27,174
4
41
69

table has a grand total of 4 integer columns and ~20000 rows – KellCOMnet Jul 14 '10 at 03:26
+1 for execution plan – Mark Henderson Jul 14 '10 at 04: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

score 1 · Accepted Answer · edited May 23 '17 at 12:41

1

It's probably parameter sniffing (StackOverflow search).

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

edited May 23 '17 at 12:41

Community

1

answered Jul 14 '10 at 04:26

gbn

6,079
1
18
21

Mysterious timeouts in MSSQL 2000

3 Answers3