up vote 0 down vote favorite
share [g+] share [fb]

On a SQL server, why do we need a weekly update stats job (with full scan) when we have AUTO_UPDATE_STATISTICS=ON for all databases?

Doesn't this option update statistics all the time?

Regards

link|improve this question
82% accept rate
feedback

1 Answer

up vote 1 down vote accepted

Yes and no, it depends on the table size and the number of rows being updated whether status updates will be triggered; an occasional rebuild is a good idea. Some info on the algorithm for auto updates

link|improve this answer
it depends on the table size and the number of rows being updated ? can you please give me an example? – Manjot May 13 '10 at 0:35
See link posted above. Stats get updated when a threshold is crossed, not after every update; it's not uncommon to not use auto_update so updates don't happen at random intervals (they're not random, but tell that to the user that just updated a record that triggered the update, causing a blip in performance while stats get updated). If you turn off auto_update, you MUST do occasional rebuilds. – SqlACID May 13 '10 at 0:45
Thansk a lot for this article – Manjot May 13 '10 at 2:00
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.