Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers 2

up vote 4 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

See MSDN, "Execution Plan Caching and Reuse"

Now, it's unclear what your problem is, but do you have parameter sniffing issues?

share|improve this answer
    
Hmm - you seem to be making a distinction between "flushing" (due to memory pressure) and "recompilations" (from statistics updates) - is there a reason/basis for this distinction? The linked article suggests that stats updates cause dependent plans to be "marked as not valid" - why wouldn't they just be flushed? Maybe this is getting off-topic. Anyway, you answered my intended question ("Yes, dependent query plans are automatically recompiled after a statistics update"); I don't really have a problem, was just adding context to the question. –  Tao Jan 26 '12 at 13:00
2  
@Tao - Updated statistics only mean that individual statements in a plan need an optimality based recompile not to flush the whole thing. –  Martin Smith Jan 26 '12 at 13:08
2  
@Tao: The analogy might be [separation of concerns] too: (en.wikipedia.org/wiki/Separation_of_concerns): the stats thingy does stats, the plan cache widget does plan cache stuff etc. As Martin says, statistics says "I've changed" and the consumer of this status decides what action to take: not the generator of this status –  gbn Jan 26 '12 at 13:11
    
Excellent, thank you both, this makes perfect sense - didn't know (and wouldn't have thought) sql server plans could be partially reused / partially recompiled! –  Tao Jan 26 '12 at 13:45
add comment

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

[See this post]: http://erinstellato.com/2012/01/statistics-recompilations/ for details, it depends on settings for Auto Update Stats. If disabled apparently the answer is NO the query will not recompile.

share|improve this answer
    
Wow, that's both interesting and timely - I guess I have to revisit that "let's not worry about whether or not my stats should be updating automatically" comment in my question now! –  Tao Jan 27 '12 at 8:49
add comment

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.