"Update Statistics used by query optimizer"

Posted on 2006-04-07
Medium Priority
Last Modified: 2011-09-20

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,

Question by:deepakee
LVL 20

Accepted Solution

Sirees earned 450 total points
ID: 16403202
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.

LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 150 total points
ID: 16403304
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

Assisted Solution

hberenson earned 150 total points
ID: 16414438
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question