If I have table in an environment, and it has not undergone any DDL changes or sp_recompile. How much statistics data has to be inserted/updated before SQL Server 2016 Engine Recompiles a query utilizing the table? I heard it is around 20%.
The old behavior was 20% churn (+500 rows). The new behavior, starting with 2016, is a more dynamic rate based on the number of rows. I don’t know that the specific intervals are published anywhere, but you could experiment to determine the exact thresholds.
The main point is, you should see auto-update stats kick in more frequently as your table gets larger. This matches the behavior introduced in an earlier version with trace flag 2371:
- Default auto statistics update threshold change for SQL Server 2016
- Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server
There are some reasons these won’t fire, though, listed in this Microsoft blog post:
- This instance tried to update the stats and failed, we should ignore the rowmodctr to prevent endless optimize-recompile loops.
- The query is being run in autopilot mode (i.e. by one of the sessions created by Database Engine Tuning Advisor to fine tune a given query or workload).
- This is a READONLY table/database.
- Trivial optimization plan was found.
- User supplied ‘KEEPFIXED PLAN’ hint.
- Internal QP plans where autostats are disabled. This avoids infinite recompiles.
- When traceflag 3608 is enabled.