I have an existing sql 2000 server that has a maintenance plan whose only step is to UPDATE STATISTICS on the OPTIMIZER tab. I also notice that all of my databases have the option, AUTO UPDATE STATISTICS turned on. With this option turned on, is it necessary to run that maintenance plan??? I'm under the impression that the auto update is doing exactly what the maintenance plan is doing on a scheduled basis
It might be.
Auto update stats means that when a query reads a set of column statistics and finds that they are out of date, it requests an update before proceeding with the query. For a set of stats to be out of date 20% of the table must have changed since the stats were last updated. That's a lot of rows on large tables.
The maint plan (as far as I know) executes UPDATE STATISTICS so it updates all stats, regardless of whether they are out dated or not.
It is possible for the auto update threshold to be too high and queries start degrading in performance due to poor stats long before the point that the auto update would kick in.
Now as we are moving our db's from sql2000 to sql2005 we are running dbcc updateusage and sp_updatestats to get our stats all cleaned up.
Don't use sp_updatestats there. Use UPDATE STATISTICS. sp_updatestats only updates stats that are considered outdated. When moving from 2000 to 2005, it's important to update all statistics.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass