In SQL Server, statistics are updated automatically when
Auto Update Statistics in
True (which is the default). Is there a reason to update statistics manually and in what circumstances?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Sure, if your data is changing more frequently than the rate of auto stats (or, say, you are updating < 20% of the rows frequently, like updating statuses or date/time stamps). Or if your table is huge and it is not changing enough to trigger auto stats updates. Or if you have filtered indexes (since the auto stats threshold is still based on the % of change on the whole table, not the % of change of the subset of rows in the filtered index).
A couple more (superficial) reasons:
Auto-update stats will block the query that triggered the update until the new statistics are ready.
...Unless you also enable auto-update stats asynchronously. Then the query that triggered the update won't wait for the new stats, but will potentially run with the old, incorrect stats.
I also ran into some strange blocking issues when a database with some relatively large tables (44M rows, 8.5 GB) would start updating stats. We decommissioned that application before I was able to really track down what was going on, though.
Yes, remember that auto stats are always generated with a default sampling rate. That default sampling rate may not accurately produce statistics that represent your data.