Tuesday, November 16, 2004

SQLServer: Statistics - Auto vs Manual update ?

Have you ever seen 'select statman(....)' running while your SQLServer is under heavy transactional load? If not, you either don't have auto update stats turned on, or you database is not really under heavy load.

By default, auto update is turned on, and SQLServer keeps track of how often indexes / tables are changed. Once 20% or 500 rows are changed, SQLServer starts updating statistics to improve performance on the chance that the distribution of data might change requiring subsequent changes to execution plans.

This is fine and dandy, except that in many cases, performance can be degridated when this happens. Question is, should we be having this set or not? And if we do have it set, should we be updating statistics on a regular basis?

We have found (beyond a reasonable doubt - even at a certain company's internal SQLServer databases) that statistics for SQLServer seem to get corrupt from the optimizers point of view over time. One theory is that auto stats may contribute to that. It's gotten so bad on some of our databases that we have to update stats every hour in order to be sure we 'fix' the situation as soon as possible after it happens. We plan on testing the difference with auto statistics on vs off on our databases and see what kind of performance differences we get and if there is any noticeable drop in corrupted statistics...

We'll let you know what we find...
Post a Comment