"Update Statistics used by query optimizer"

Posted on 2006-04-07
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


    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
    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
    LVL 3

    Assisted Solution

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    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.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now