"Update Statistics used by query optimizer"



I’ve created a Maintenance job using the Database Maintenance Plan Wizard that reorganizes data and index pages.

It has been scheduled to run on a weekly basis.

Now, I want to create another Maintenance job in order to “Update Statistics used by query optimizer”.

When this “Update Statistics used by query optimizer” job should run? Should it be scheduled to run just after the rebuilding indexed job?

Or can it be scheduled to run the day after? Would it be OK to set the “Sample” option for the update statistics job to 100%?

And this job, is it the same as running sp_updatestats?

Thank you,
Deepak

deepakeeAsked:
Who is Participating?
 
SireesCommented:
Source: http://www.sql-server-performance.com/statistics.asp


According to a SQL Server tuning expert at Microsoft (one of their best), it was suggested to me that if your SQL Server maintenance window allows for it, that you should update statistics for all tables and for all databases every night. This is because the auto-update option is less than perfect, and accurate and up-to-date statistics is so important to overall performance of SQL Server. Of course, if this is not possible because of time constraints, then you will have to perform this step less often, or even not at all, and rely on the auto-update feature. But if you do have this window, you should take advantage of it.

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
DBCC DBREINDEX will automatically updates the stats.. So it will be better if you run the 'update stats' once in two days.. agin this will depend on how often your insert operations are going. If you are having too many inserts then you can schedule it tto a smaller interval... remember this will consume a lot of processor resourse, so better schedule it when u have a less hit on ur site
0
 
hberensonCommented:
The only difference between an auto-update and a manual update is what triggers the update.  Manual happens when you say, auto happens when a certain percentage of the rows in the table change.  So at this level there really is very little difference.  What can be different is that auto-update generally samples the table rather than scanning the entire table since you wouldn't want the auto-update to wait for an entire table scan.  With a manual update you could optionally scan the entire table (or at least increase the percentage of the table that is sampled).  This is what could make a difference, in a small percentage of tables.  The problem with the sampling technique is on tables with dramatic amounts of skew the samples might not reflect the real distribution in the table.  This is rare, but if you have such a table then you may want to go to a manual update with a full scan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.