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

http://blogs.sqlservercentral.com/andy_warren/default.aspx