Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

share|improve this question
    
"Is there a way to figure out which plans depend on a given index's statistics?" I don't think so but you might find this interesting – Martin Smith Jan 26 '12 at 13:10
    
@MartinSmith: thanks, this is indeed very interesting! Thankfully I don't need to worry about it as my concern (incorrect plans based on since-updated stats) appears to be unwarranted. – Tao Jan 26 '12 at 13:48
up vote 5 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.