0

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

1 Answer 1

1

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

3
  • it depends on the table size and the number of rows being updated ? can you please give me an example?
    – Manjot
    Commented May 13, 2010 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
    Commented May 13, 2010 at 0:45
  • Thansk a lot for this article
    – Manjot
    Commented May 13, 2010 at 2:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .