It’s time. High time.
Time to set the record straight on types of execution plans.

There are some severe misconceptions, and they have many causes. One cause is many good folks, including myself, have in the past spread incorrect information. We were young, we didn’t know any better. But now we do, and we want to set the record straight.
Another cause is terminology. Terms, originally chosen by Microsoft and then used by everyone else, suggested something not quite true. And we all fell for it, me included. But not anymore. It’s time to set the record straight. And to choose new, better terms, to prevent similar confusion going forward.

This post is not just my opinion. This post is a co-production by Erin Stellato, Grant Fritchey, and Hugo Kornelis, and partially based on extensive discussions we had with Microsoft employee Pedro Lopes, who works on the team responsible for, among other things, execution plans.

There can be only one

The primary goal of this post is to clarify, once and for all, that there are NOT multiple types of execution plan. Yes, you have been told that there are. Yes, the internet suggests there are. The tools suggest there are. But it’s not true. There is only one type of execution plan. Period.

This does not mean that there cannot be more than one plan for the same query. Under the right circumstances, a single query can have dozens, perhaps even hundreds of plans. They are different plans. Not different types of plans.

The same query can get a different plan due to recompilation. When any of the many inputs for the cardinality estimator and the optimizer change, you can get a different plan. Another reason can be different SET options, different Resource Governor pools, or even users with different default schema executing the same query, if not all object names are schema-qualified.
But while these plans are different plans for the same query, they are all still the same type of plan. Because there is only a single type of plan: the execution plan.

Let’s try to better understand better why there is only a single type of plan, why it can be easy to be misled into thinking that there are multiple types of plan, how currently accepted terminology contributes to that, and how better terminology might help prevent that. Let’s follow the life of an execution plan.

An execution plan’s life

The life of an execution plan starts before it even exists. It begins when a query is submitted to SQL Server for execution. To the user, this appears quite simple: you type a query, hit execute, and after some time you see the results. But a lot of things happen under the cover to make that happen! (Note that the description below is simplified in a few areas that don’t contribute to the point we are trying to make in this post).

1. SQL Server checks to see whether a valid execution plan for the query exists in the plan cache. Since this is a new query, that will not be the case. (If a valid, existing execution plan were found here, it will be retrieved and step 2 will be skipped.)
2. SQL Server next starts a complex and expensive process called compilation, which results in an internal structure that determines how the query should be executed. This is the execution plan.
3. If you submitted the query with a request to return the so-called “estimated execution plan”, then the process stops here. The execution plan returned to the client, not in native internal format but as XML. The client can then parse the XML and output the execution plan in whatever format it wants, for instance the graphical format that SSMS uses.
4. Just before the query starts to execute, the execution plan will be stored in up to three internal locations: in the plan cache, in the Query Store, and optionally in memory, to support the query profiling infrastructure.
   For each location, there may be conditions that can cause some plans to not be stored; these details are beyond the scope of this post.
5. Execution starts. The SQL Server engine executes the instructions in the plan. During execution, the engine uses run-time counters to collect various metrics of the execution.
6. In some specific cases, a recompile may be triggered for one or more queries within a larger batch, or in one case (Interleaved Execution) even for an area within an execution plan. If that happens, steps 2 and 4 above are simply repeated for the relevant (part of the) query. The result is a new execution plan. It is the same type of plan (you can’t tell from looking at the plan whether it was generated from a regular compile or a recompile), but due to changes in the input the exact shape of the plan (operators used, data flows, and properties) can be different.
7. After the query execution is finished, the engine stores the final values of the run-time counters mentioned in step 5 in up to three internal locations: the DMVs that are tied to (but not exactly part of) the plan cache, in the Query Store, and in memory if the LAST_QUERY_PLAN_STATS database scoped configuration is enabled.
   If the submitter of the query requested the so-called “actual execution plan”, then the execution plan is returned to the client, again not in native format but as XML, but in this case with the final values of the run-time counters are added to that XML. The client can then use the XML to show the user a visual (or other) representation of the execution plan.
8. The copies of the execution plan that were stored in the plan cache, in the Query Store in step 4 remain there even after the query has finished execution. Both locations have (quite different) rules to determine when execution plans get flushed, so they are not necessarily there forever, but they might be there for a long time!

Estimated, actual, live, and more…

When we check out execution plans for a query, we have a wealth of choices of how to collect the execution plan. And what we see often depends on how we choose to request the execution plan. That, combined with the different naming of these options, is why many people strongly believe that they are, in fact, different types of execution plans.

In SSMS, the Query menu alone offers what appear to be three types of execution plans: “Estimated Execution Plan”, “Actual Execution Plan”, and “Live Query Statistics”. Each of those three displays different information. It is easy to believe that they are different types of plan. And yet, they are not.

Below we will tie those three suggested execution plan types to the life cycle of an execution plan as outlined above, to try and clarify. After that, we will look at a few other ways in which execution plans can be collected.

“Estimated Execution Plan”
When you request an “Estimated Execution Plan”, the lifecycle above starts, then simply stops after step 2. The plan you request is either generated or retrieved from the plan cache, then returned to the client. That’s all.

“Actual Execution Plan”
When you request an “Actual Execution Plan”, the lifecycle starts and then completes, and step 7 returns the execution plan, now enriched with data collected during execution, to the client. The plan itself is (barring recompilation) still the same plan retrieved from cache or compiled in step 2, the only difference is that the run-time metrics are added in this case.

“Live Execution Plan”
When you request a “Live Execution Plan”, the process is bit more complex. The client submits the query, and the entire lifecycle runs. But instead of patiently waiting for the query to finish execution, SSMS continuously collects data from the server while the query executes. The data collected is the plan (as retrieved from cache or compiled in steps 1 and 2), along with the current values of the run-time metrics collected in step 5. These values are then updated every second on your screen, allowing you to track the execution progress.

Regardless of which method you use to retrieve the execution plan, you will always get the same execution plan. The plan that was either retrieved from cache, or just compiled. The only scenario where the plan would actually be different is when there is a recompile in between the two retrieval moments, which is expected behavior. Execution plans do evolve over time. It’s still the same type of plan, just one that was compiled later.

Other methods
There are more ways to retrieve execution plans than just the methods above.

You can see plans through the UI of the Query Store or by querying the underlying tables of the Query Store. Those plans are stored in step 4 – the same execution plan, just copied elsewhere.

You perhaps have, in your library of useful snippets, one or more scripts that present problem queries, active now or in the past. Those scripts always use DMVs that expose data stored in the plan cache, and some associated counters. Again, the same plan, stored in the plan cache in step 4.

You may have used Extended Events, or even Profiler, to capture execution plans. Depending on the exact event used, what you get is either a copy of the plan after step 2 (compilation) finishes, a copy of the plan at step 4 (just before execution), or a copy of the plan, enriched with run-time statistics, at step 7 (after execution).

You may use some third-party software to monitor your servers. Many monitoring tools allow you to see execution plans of queries that are running, or that were active at some time in the past. But those tools don’t do magic. They do the same things you and I can do. You probably have a collection of scripts to query the various DMVs in SQL Server for performance related data, including execution plans. Those tools use similar scripts, automated and continuously: they poll the plan cache, poll other DMVs to add data about duration and resource use of those queries, and then present that information in their dashboards.

Better names?

We hope that, with the explanation above, you now understand that there really is just a single type of execution plan. However, the existing terminology, especially for “estimated” and “actual” execution plan, still suggests otherwise. We believe that the best way to prevent this wrong perception going forward is to stop using these terms.

Based on discussions between Erin, Grant, Pedro, and Hugo, and then on additional input from a Twitter poll, we propose to switch to the following terms going forward:

• Execution plan. This is what we used to call the estimated execution plan. It is just the plan for how to execute the query, captured before execution, and hence without additional data. It can be retrieved from various sources. If you really want to make sure that there is no confusion with the other terms, you can use “execution plan only”.
• Execution plan plus run-time statistics. This is what is currently confusingly called the actual execution plan. It is the plan for how to execute the query, enriched with data about one single execution (typically, the most recent execution just before the plan was captured). This version of the plan can also be captured from various sources. If you want to save time, we suggest shortening this to “execution plan plus”.
• Execution plan with live statistics. This is then of course our suggested replacement term for “Live Execution Plan”. It is the plan for a currently executing query, that is constantly being updated with run-time statistics as the plan is progressing. For those who prefer shorter terms, we suggest “live execution plan” in this case. (See, we do not necessarily want to change all terms).
  At the time of writing, the live execution plan can be seen in SSMS by executing a query with the corresponding option on, or by requesting a “live execution plan” from Activity Monitor. Azure Data Studio does not support it. However, since the infrastructure is available for all to use, SentryOne already has added similar functionality to their Plan Explorer tool, and other third-party tool vendors can do the same if they choose to.

Bottom line and call for action

We believe that a lot of confusion and misunderstanding can be prevented by properly educating the SQL Server community that there is only one type of execution plan. We also believe that such education is hampered by the current terminology.

We call for all people active in the SQL Server community to stop using the terms “estimated” and “actual” when discussing execution plans, and use the proposed new terminology presented above instead.

And finally, we call for Microsoft and for third-party tool vendors to adopt these new terms, and change the corresponding terms in all their tools, to help the community adopt these new terms as soon as possible. If you agree, please let Microsoft know by voting for our suggestion!