Exploring the Auto Update Statistics Option

SQL Server 2000's ability to automatically update statistics for my tables and indexes is enabled by default. How often does SQL Server perform this update, and does the action have a negative effect on performance?

The auto update statistics option can slow down your system, but don't disable the option simply because you're concerned that it might affect performance. More often than not, keeping this option enabled is the correct decision and will lead to substantially better performance. If you load a lot of data into a table during a peak processing period, SQL Server's automatic statistics update on that table might further degrade performance. In this case, you can disable the auto statistics option for the table during the load, then manually run UPDATE STATISTICS when the load is finished. However, you might find that certain queries begin to choose inefficient query plans if you've disabled the auto statistics option for the table, which could cause the statistics to no longer reflect the accurate distribution of data in the table and result in SQL Server choosing a suboptimal execution plan.

SQL Server 7.0 and earlier releases don't have the automatic update statistics option. With these releases, the most common solution to a sudden query-performance decrease is to update your statistics. I recommend leaving the auto update statistics option enabled unless you have a reasonably strong SQL Server performance-tuning background, can measure the before and after effect of disabling the option, and have benchmarks that prove disabling the option makes sense in your environment.

As for how often SQL Server performs this update, see the Microsoft article "INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work" at http://support.microsoft.com/default.aspx?scid=kb;en-us;195565&product=sql. This interesting article covers the algorithms SQL Server uses to choose when to automatically update statistics. The timing depends on the number of rows in the table, the number of rows affected by queries since the last statistics update, and whether the table is permanent or temporary.