1

I'm currently considering using Ola Hallengren's Index Maintenance solution to update statistics once a day. What is the benefit of this solution if a database has AUTO_UPDATE_STATISTICS set to on?

I'm on SQL Server 2016.

1 Answer 1

1

Automatic statistics maintenance is based on a formula (depending on which version of SQL Server you're on) that determines which sets of statistics will be updated when. Follow this link and scroll down to the AUTO_UPDATE_STATISTICS option to see the specific formula.

As long as your data very neatly fits within the formula, no, you don't need to additionally update statistics. However, you're going to find that some tables, some indexes, some column statistics, will need more frequent updates. You may also find some statistics don't benefit from the sampled scan that the automatic statistics maintenance provides so you'll have to do a full scan.

In short, the automatic statistics maintenance covers most cases, but it doesn't cover all cases. So, you bring on manual (probably automated) updates. Once a day may work, but you may find that some sets of stats need more frequent updates, or different updates. You should simply prepare to be flexible on this, knowing that the automatic maintenance gets 90-98% of the cases, and you'll need to cover the rest.

2
  • Thank you, that clears it up. So would you say that applying the update stats portion of the Index Maintenance solution probably isn't warranted if I'm not aware of any existing issues with statistics?
    – Marcus
    Commented Jun 1, 2023 at 11:44
  • 1
    Sorry for the late reply. If it ain't broke, don't fix it. If you're monitoring your servers and you know performance is adequate, I wouldn't charge in and start applying "fixes". I'd instead let the monitoring and the information gathered, guide me in my choices as to what/where/how/when I go about intervening. Commented Jun 6, 2023 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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