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 can think of many reasons behind this decision of storing estimated plans in the plan cache and not the actual plan. But I can't find the "correct" answer.

share|improve this question

migrated from stackoverflow.com Jun 8 '13 at 13:34

This question came from our site for professional and enthusiast programmers.

3  
I think you've mistaken the purpose of the plan cache - as Thomas explained, it's main function is to serve as a map of what SQL Server should do, not a history of what it has done. And since the actual can contain runtime metrics, would you want a copy of the plan stored in cache, for every single time it was executed? What purpose would that serve? And at what cost? –  Aaron Bertrand Jun 8 '13 at 16:13
2  
Can you list the reasons you think there are, and explain why you think none of them are "correct"? What does "correct" mean? –  Aaron Bertrand Jun 8 '13 at 21:39
    
@AaronBertrand I was also thinking on the same lines as thomas and you explained. –  manish Jun 9 '13 at 6:45
add comment

1 Answer

up vote 10 down vote accepted

Think about what "actual" means. It's what actually happens for the execution of that plan.

Another common name for the actual execution plan is the "post execution plan". As a real world example to correlate this scenario, say you plan to go on a cross country trip, so you plot out the roads you're going to take and how long you think it'll take. But because of road work and detours, it doesn't happen quite like you planned. That's the actual execution plan: The actual route that you took to get to where you're going. The estimated plan would have been what you thought you'd be doing, and what route you thought you should take.

You can't store post execution plans because they are just that....post execution. They contain metrics of what really happened, not what SQL Server thinks should happen (estimated execution plan).

The plan cache is really just a repository of "maps" that it can reuse without having to spend the expensive time to draw up a new one. Actual execution plans are after the fact, and the plan cache is to handle before the fact.

share|improve this answer
    
actually no. THEORETICALLY the first finished execution could go back and replace the plan. Like you may decide to plan the same route different second time, learning. Not saying it is wise, just that the argument you give is only valid until the plan execution finished. –  TomTom Jun 8 '13 at 15:02
3  
Right, there's a handful of scenarios that could cause that. But without going into a deep dive on plan caching I believe a gave a good 10,000 foot view. To get into all the nuances of plan caching would take way more than a SE answer. –  Thomas Stringer Jun 8 '13 at 15:51
    
@ThomasStringer What you said is making perfect sense. I got the idea. Thanks –  manish Jun 9 '13 at 6:47
    
No problem, glad to help! –  Thomas Stringer Jun 9 '13 at 16:57
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.