How much Table Statistics Change before Query Execution Plan Update?

Posted on

Question :

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%.

Thanks,

Answer :

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:

There are some reasons these won’t fire, though, listed in this Microsoft blog post:

  1. This instance tried to update the stats and failed, we should ignore the rowmodctr to prevent endless optimize-recompile loops.
  2. 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).
  3. This is a READONLY table/database.
  4. Trivial optimization plan was found.
  5. User supplied ‘KEEPFIXED PLAN’ hint.
  6. Internal QP plans where autostats are disabled. This avoids infinite recompiles.
  7. When traceflag 3608 is enabled.

More info here and here.

Leave a Reply

Your email address will not be published. Required fields are marked *