We all have worked with performance tuning and troubleshooting at different levels. As a result I assume we all worked with execution plans or query plans. When we talk about this subject more, it is noted that authors are using many terminologies sometime confusing when cross referencing the articles and try to correlate one another. So thought about publishing a blog post on this.
So question is compiled plan, execution plan and query plan are all same or different.
What is Compiled Plan?
It is a known fact that when SQL Server executes a query / statement / batch, it first creates something called a plan. What is this plan? Is it a compiled plan or execution plan?
To understand this, we need to think more about query execution. Any query before it executes, it has to pass compilation stage. The output of this stage is compiled plan. Compiled plan is generated and stored in plan cache. (Plan cache is a separate area of SQL Server memory)
You see the compiled plans created by SQL Server using the following DMV. The plan_handle is used to identify a compiled plan uniquely.
sys.dm_exec_cached_plans
What is Execution Plan?
After compilation stage, then comes the execution stage. This is the stage actual execution of the query occurs. So it substitutes the run time parameter values and create a execution plan. Therefore execution plan is a run time object.
To create a execution plan, there should be a compiled plan. So that execution plan is a dependent object of compiled plan.
The beauty of of the execution plan is, SQL Server generates an execution plan using the compiled plan, each time when the query executes. Then that execution plan has runtime parameter values. So unlike compiled plans execution plans are single use. When N users are executing the same batch, there will be N no.of execution plans. However as per MS, the generation of an execution plan is not that costly operation like generating a compiled plan.
When each user executes a batch, the parameter values are different than compiled plan. So that the user specific data should be maintained separately. The data structure uses for this purpose is called, Execution Context. The msdn definition is as below:
“Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context. The execution context data structures are reused. If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.”
Figure:1 Execution Context (courtesy of msdn)
So sounds like Execution plan and Execution context similar? (I do not know the answer yet)
What is Query Plan?
As msdn states, I believe query plan is another name for compiled plan. The definition given as below;
“The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.”
Conclusion
I referred the following links to understand the concepts and more into internal stuff. Unfortunately, I did not find much places to see more detailed explanations about the topic. I appreciate the readers input into the topic if they know more about it or have some contradicting information in the content.
Cheers.
References
http://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/09/1-0-structure-of-the-plan-cache-and-types-of-cached-objects.aspx
