Auto_Update_stats: Not helpful on large tables
Experienced DBAs know that auto-update-stats is not sufficient to keep statistics updated on large tables. This is doubly true for tables where the DML activity is near one end of the clustered key.
There are some common workarounds:
Stale stats, you have failed me for the last time!
- Have all DML operations run an update statistics command after they’re done. However, this can require a lot of code changes.
- Update all statistics on a schedule, using sp_updatestats. However, this can lead to bad blocking, and often can’t be run often enough.
- Swearing at your systems. However, too much will get you put in management.
Not content with any of these, I’ve gone ahead and built my own script:
Uber-Update-Stats
Script: Uber-Update-Stats.sql
Unit Test: Uber-Update-Stats-Test.sql
Now I can run this script on a near-constant schedule, and it will update each table as soon as a configurable percentage of rows or number of rows have changed. You can also configure the sampling percentages. I think it is The New Hotness very useful.
Coming up next: A version that compensates for filtered indexes & filtered statistics.