Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 8896 | Views in the last 30 days: 2
 
Related Articles
BLOG

RYO Maintenance Plan – Update Statistics

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

FORUM

HELP= Rebuild index & Update STATISTICS

HELP= Rebuild index & Update STATISTICS

FORUM

creating index and updating statistic

is it necessary to update statistic after creating an index ?

BLOG

Statistics Update Clarification

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

FORUM

maintenance plan on sql server 2000 - statistic

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

Tags
indexing    
statistics    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones