Most of know that statistics need to be updated at some interval (weekly as a
minimum in most cases) and it's common to use the built in maintenance plans to
do the update. But did you know that doing it that way could lead to your stats
being updated twice instead of just once? Or that sp_updatestats doesn't do the
equivalent of running 'update statistics' on every table in your database?
On the options tab of create index you can see a default setting that no one
pays attention to; automatically recompute statistics, show below as well:
What that means is that each time you rebuild (not reorg/defrag) the
statistics related to the index will be built at the same time. That makes a lot
of sense since we have to scan through every row in the table to build the
index, and that gives the ability to calculate new stats with a 100% sample.
Below I've captured the update statistics task portion of a maintenance plan.
The default is 'All existing statistics' which would update all stats, both the
ones based on indexes as well as the system/manually generated ones. A better
option is to change it to 'Column Statistics Only' so that we don't repeat the
work we just did (or are about to do) during the index rebuild.
Full scan is the default and one I recommend unless you reach the point where
the run time or IO cost becomes prohibitive. If and when that happens you can
reduce the sampling accordingly. In many cases you can do 50% or less sampling
with no issues.
Now let's move to sp_updatestats. This was handy in SQL 2000, a way to
quickly rebuild all your stats on a database. The behavior changed in SQL 2005
to only update the stats that need updating - not even close to the same
behavior. If you're relying on this proc you should consider replacing it with
the equivalent maintenance plan step, or custom code to do the same.
Just two quick tips, but I hope they prove helpful.
I blog frequently at