Rebuilding Stats: Twice or Not At All

, 2008-07-29

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

 

Rate

4.12 (33)

Share

Share

Rate

4.12 (33)

Related content

Statistics and Indexes

When is it okay to let SQL Server to make a statistic for columns in your queries, and when should you take those statistics and make an index out of them? What I do is to test all of my procs, and if they generate system statistics (_WA_SYS%), then I add an index to the table for the column in the statistic. But is this a good practice?

2008-03-11

3,488 reads

Clustering for Indexes

There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.

2004-12-23

3,412 reads