June 1, 2010 at 4:54 am
I'm currently working through the 70-433 (Database Development) material and yet again I've come across something that I though I understood, but apparently don't. Not a problem, as this is one of the reasons I'm taking this course. However, this time I'm having problems getting my head around the explanation both within the book I'm using and BOL.
Basically, I always thought that an Execution Plan was created for segments of code, which could be re-used by SQL Server. They could be recreated by recompilation, triggered by a variety of actions (WITH RECOMPILE, stats well out of date, aged out of the cache, table changes and so on).
Now I'm reading about a Query Plan, stored in the query cache - reused each time the procedure is executed. Alright, so this sounds like what I was referring to as the Execution Plan, so perhaps I was using the wrong terminology.
But then the book states "Each concurrent execution of a stored procedure also generates a query plan for execution, which is called the execution plan". So is Query Plan and Execution Plan one and the same?
In BOL it also refers to Execution Context, which just confused me that little bit more, as it later refers to Execution Plan.
So basically, Query Plan, Execution Plan and Excution Context - what are they and how long does each 'live' for?
Thanks for any assistance is clearing this up for me.
BrainDonor.
June 1, 2010 at 7:01 am
Hey BrainDonor,
I sympathise...it is complex, but at least logical.
There is a full (and logical) explanation in one of my favourite references:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
If any questions come out of reading that, please ask!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 1, 2010 at 8:02 am
It is quite dense in places, and there's a decent high-level summary here:
Execution Plan Caching and Reuse
(It contains a link to the document I pointed you at before, for those wanting more detail).
Once you have processed that, the hardcore stuff is here:
Structure of the Plan Cache and Types of Cached Objects
That's pretty deep in places.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 1, 2010 at 8:21 am
So in summary
Execution Plan = Query Plan + Execution Context
Context is used specific information in terms of parameters and other data structures that could change for that proc/batch.
June 1, 2010 at 9:10 am
Steve Jones - Editor (6/1/2010)
So in summaryExecution Plan = Query Plan + Execution Context
Context is used specific information in terms of parameters and other data structures that could change for that proc/batch.
I see what you're trying to say, but it is important to get the terminology right (this is for an exam after all).
There is one executable plan for every executable statement in the compiled plan.
Executable plans contain 'context information' like local variable variables, dynamically-created object ids and so on.
The terms 'executable plan' and 'execution context' are synonyms.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 1, 2010 at 9:19 am
I think you're right, but I'm not sure the exam delves this far down. From what I remember, execution plan is pretty much used for the query plan as a synonym, and includes the entire proc/statement. It doesn't talk about context, and until I followed that link, I don't think I've seen that term used. I've known there are parts that are replaceable (or initializable as in the link), but never heard them referred to as part of the execution plan.
I think that an "executable plan" starts to get incredibly confusing to people and shouldn't be used.
June 1, 2010 at 9:35 am
Steve Jones - Editor (6/1/2010)
I think you're right, but I'm not sure the exam delves this far down. From what I remember, execution plan is pretty much used for the query plan as a synonym, and includes the entire proc/statement. It doesn't talk about context, and until I followed that link, I don't think I've seen that term used.
That is surprising. Is it a pretty basic exam then?
I think that an "executable plan" starts to get incredibly confusing to people and shouldn't be used.
I can't agree with that. That term is technically correct and has a precise meaning within SQL Server. Perhaps it shouldn't be used when talking casually, or with people at an early stage of development...I don't know.
As far as I'm concerned, it's never a bad thing to know too much 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 1, 2010 at 9:43 am
I think you're right, but I'm not sure the exam delves this far down.
I suspect that is true, but personally I hate to just 'skip over' something that is causing me difficulties. The links from Paul are excellent and I'll read them until my ears bleed or until I'm happy that I have an understanding of the subject.
They could promise me that it will never delve this deep, but if it gnaws at the back of my mind I just have to do something about it.
BrainDonor.
June 1, 2010 at 9:44 am
BrainDonor (6/1/2010)
I think you're right, but I'm not sure the exam delves this far down.
I suspect that is true, but personally I hate to just 'skip over' something that is causing me difficulties. The links from Paul are excellent and I'll read them until my ears bleed or until I'm happy that I have an understanding of the subject.
They could promise me that it will never delve this deep, but if it gnaws at the back of my mind I just have to do something about it.
You are now officially 'my kind of person'! :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 1, 2010 at 9:55 am
BrainDonor (6/1/2010)
You are now officially 'my kind of person'! :w00t:
And that's a good thing.....?:hehe:
As always, "it depends" 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply