SQL Server: Do Statistics Updates cause query plans to be flushed? Should they?

Question

I've found that a big, nasty data-extraction query that runs daily needs updated stats to avoid making horrible query plans based on incorrect rowcount estimates (let's not worry about whether or not my stats should be updating automatically).

My question, as noted in the title, is: Should I be concerned about incorrect query plans sticking around if a query plan happens to be prepared before a given set of statistics is updated, at a time when the desisions made by the optimizer turned out to be wrong?

Or to stats updates automatically cause dependent query plans to be flushed?

If the plans stick around, is there a way to figure out which plans depend on a given index's statistics? (I know, I could go digging in the DMV docs, just hoping someone already has the answer)

Answer 1

The answer is close to your "stats updates automatically cause dependent query plans to be flushed". They don't "stick around"

• Flushing a plan from cache is determined by memory pressure.
• Statistics updates cause plan recompilations if AUTO_UPDATE_STATISTICS is on.
• Updated statistics only mean that individual statements in a batch plan need an optimality based recompile, it does not flush the whole thing.

The analogy might be separation of concerns: the stats thingy does stats, the plan cache widget does plan cache stuff. Statistics says "I've changed", and the consumer of this status decides what action to take: not the generator of this status.

See Execution Plan Caching and Reuse on MSDN.

Answer 2

Came across an interesting article this morning which adds the ever present "it depends" to the answer. See this post:

Statistics and Recompilations by Erin Stellato

...for details, but it depends on settings for Auto Update Stats. If disabled apparently the answer is no, the query will not recompile.

From Execution Plan Caching and Reuse on MSDN:

When the AUTO_UPDATE_STATISTICS database option is SET to ON, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution. This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. If query performance is affected by excessive recompilations, consider changing this setting to OFF. When the AUTO_UPDATE_STATISTICS database option is SET to OFF, no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers.