Indexes degrade too fast

(Win2003, SQL Server 2000 SP4)

I have a database of about 5Gb of size. Some queries where taking more than 1 minute to complete execution (all of them are stored procedures). Because of that lack of performance, I call the command DBREINDEX for each table, executed the sp_updatestats system stored procedure and finally I executed the sp_recompile system stored procedure for each sp in my database.

After all this task, queries completed in a matter of a few seconds instead of minutes. Strange enough is that some hours later (about 6 hrs), after normal use (this database belong to a Client/Server information system), the problem appeared again: Queries started to take too long to complete.

I am assuming that indexes are degrading too fast so that they required another ReIndex, but I am not sure.

Any thoughts? How can I prevent this behaviour?

Thank a lot in advanced.Have a look at the execution plan before and after the re-indexing routine. This will give you the "good" plan, and the "bad" plan. If the plans are identical, then the reindexing helped (doubtful but possible on a 5Gb system). More likely, the sp_recompile bumped the plan back from the "bad" plan to the "good" plan. By now, you are wondering if I just like putting "good" and "bad" in quotes. Well, no, I am not particularly fond of it, but it does remind you that not all plans are created equal. Can these procedures take in parameters that can determine a range? If so, then the "bad" plan may be good for some ranges, and the "good" plan may be bad for the same range.|||Thanks. What I really find confusing is why the performance of the database degrade in a matter of just 6 hours or so.

Of all the customers that I have distributed the same DB design, this one is the only one that presents this problem; and the size of the database is not the issue: I have other customer that has a 15 Gb database with the same DB design, then same DBMS version but with a not-as-good hardware. However, this customer does not have the same problem.|||What does DBCC SHOWCONTIG show you on the tables in question once the slow down restarts? Do you see high fragmentation numbers and low density numbers? Then yes your indices have degraded. have you traced the 2 systems. Does the one that is experiencing the issue have more insert and updates goinng on? Is it growing at a faster rate?|||Thanks. I will check that out. Just in case: Which DBCC command allow me to defrag all the tables of a database? I am planning to run that command and later run a DBCC REINDEX command for each index of every table. I suppose that this would produce a clean database, am I right?

Thanks in advance.|||There is no command to rebuild all of the tables/indexes at once. You can create or google for scripts that will do that for you, however.