SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Rebuilding Stats: Twice or Not At All

By Andy Warren,

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


Total article views: 8918 | Views in the last 30 days: 1
Related Articles

RYO Maintenance Plan – Update Statistics

Another key component of any good maintenance plan is updating statistics. Statistics are what help ...


HELP= Rebuild index & Update STATISTICS

HELP= Rebuild index & Update STATISTICS


creating index and updating statistic

is it necessary to update statistic after creating an index ?


Statistics Update Clarification

By default statistics are created automatically within SQL Server. And, by default, these stats are ...


maintenance plan on sql server 2000 - statistic

is it necessary to update statistic if we have a maintenance plan on sql server 2000

statistics (data)