"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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.