Statistics constantly need updating

[rsai]

Hi

In the last few days we have been experiencing serious performance problems with a database on SQL server 7. Query/ Update & Backup times have been increasing exponentially. ( Backup from 1 hour to 13 hours etc). There has been no radical increase in the amount of data being loaded into the system as opposed to normal. Viewing the query plan in Query Analyser did not return any results in half an hour after which I terminated the query.

Having identified a query which should have returned in under 1 minute and was not returning at all I updated the stats on the tables involved in the query "UPDATE STATISTICS XXX" and the performance returned to normal, backups were back to their normal times etc.

That was 5 days ago, since then stats seemed to stay OK for a couple of days and then the performance died again. The database is set to Autocreate statitics and Auto Update statistics. I am rerunning the UPDATE STATISTICS to get around my imediate problem but has anyone got any ideas as to what is causing the stats to suddenly go so far adrift.

Thanks

 

[NoCoolHandle]

It seems to me that you really need to reindex your tables at this point. Updateing the statistics just helps the optimizer not choose an index when it is disgustingly fragmented. It sounds like you need to defragment your data and that requires you reindex the worst tables.

Use DBCC Showstatistics to see how bad things are and dbcc dbreindex to rebuild.


Things should run much better again.


Rob