Statistics auto-updates are generally a good thing–auto-update of statistics is the default behavior in SQL Server. Having the freshest information about the data “shape” helps to inform the best selection of an execution plan for queries.
Except for when it doesn’t.
Because It Depends™️
Let’s talk about the trade-offs.
Auto-updated stats are triggered by queries needing to access a table with “stale” stats. (It doesn’t matter how we define stale, so lets not think about it.) With the exception of very small tables, auto-updated stats use a random small data sample of the table data; in contrast scheduled jobs that usually have a larger sample size defined—I always say “FULLSCAN if you can!”
Also, keep in mind that the sample size decreases as the table gets larger–when your query needs fresh stats, you can’t afford to scan terabytes of data to do full table scans–so you just grab a little sample. Very small sample sizes may no longer be representative of the data as a whole, creating statistics skew. This is very much similar to the way political polls can never be fully accurate–and those samples are not random.
Statistics updates trigger cached execution plans to be invalidated (and thus re-evaluated/re-computed) by the Database Engine for queries accessing the affected table. Conceptually, this ensures all execution plans always use the best, freshest information for the best performance. Except that “freshness” isn’t the only factor in “best” — life is more complicated than that.
For very large tables, this essentially gives us two choices for these large tables:
- Allow auto-update stats on very large tables in between scheduled stats updates. Do this when “freshness” is your top priority.
- Rely solely on scheduled stats updates on large tables. Do this when the expectation is that the execution plan remains stable for long periods of time, and larger sample size is more important than freshness.
Note: I do not count “auto-stats only” as an option. If that works for you, great.
I still don’t turn off auto-stats for the whole database though. It’s great for the majority of statistics and tables. It’s great for column stats. But if you have troublemaker queries that keep getting bad plans in the middle of the day, and causing issues? Consider disabling auto-update stats for certain tables/stats/indexes.
How do I turn off auto-update stats on a specific table or index
NORECOMPUTE
The simple way is to set statistics to NORECOMPUTE:
UPDATE STATISTICS...WITH NORECOMPUTE;
For indexes, it can also be done during an index rebuild:
ALTER INDEX...WITH (STATISTICS_NORECOMPUTE);
If you want to update stats for all the index and/or column stats on a table, you can also check out dbo.Set_StatisticsNorecomputeByTable from my open souce DBA database
Leave a Reply