SQL Server – Do Statistics Updates Cause Query Plans to be Flushed?

parametersql serverstatisticsstored-procedures

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)

Best Answer

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.

Related Question