Change execution plan terminology
SQL Server has since almost forever used the terms "Actual Execution Plan" and "Estimated Execution Plan" for execution plans with and without added run-time counters. And this has since almost forever caused confusion and misunderstanding.
Erin Stellato, Grant Fritchey, and me, with the help of Pedro Lopes, have prepared a proposal for better terminology. Terms that better describe the reality and have less chance for confusion.
See https://sqlserverfast.com/blog/hugo/2020/02/stop-the-name-calling-execution-plan-terminology/ for a more detailed explanation.
Together with Erin and Grant, I request Microsoft to change the terminology in their tooling. In the next release of Management Studio, please replace "Estimated Execution Plan" with "Execution plan" (or "Execution plan only" for more clarity); replace "Actual Execution Plan" with "Execution plan plus run-time statistics" (or, for short, "Execution plan plus")l and use "Execution plan with live statistics" or "Live execution plan" for what is currently know as "Live Execution Plan".
Also, in the next release of Azure Data Studio, please replace the text on the "Explain" button with "Request execution plan", and change the Command Palette option "Run Current Query with Actual Plan" to "Run current query with execution plan plus run-time statistics"
Hugo Kornelis
shared this idea
7 comments
-
Hugo Kornelis
commented
Two points of clarification are needed.
First: The phrasing of the suggestion may make it appear as if the new proposed names are endorsed by Pedro Lopes. This is not correct. The suggestion comes from Erin, Grant, and me; Pedro has assisted us by reviewing the blog post and giving suggestions but did not sign off on the suggested new names.
Second: The main purpose of this suggestion is to replace the names "Estimated Execution Plan" and "Actual Execution Plan" with something better, something that does not reinforce the incorrect notions that estimated plans are not real, that they will not be used because they are not the actual plan, or any of the other common misunderstandings related to these names. The replacement names are our suggestions, but they are not the core of this suggestion. If other names are considered better, then please use those. Just make sure that the new names will not be prone to the same, or new, misunderstandings.
-
Sean Gallardy
commented
I'd agree that the proposed naming makes things worse, not better. I don't find too many people that are confused between the current nomenclature except for what version holds what information in the data returned.
This seems completely unnecessary.
-
Joe Obbish
commented
I didn't say that it was a different type of plan. I said that "Execution plan plus" is not a good description for what goes on with that query in SSMS. I already know what the repro does.
-
Hugo Kornelis
commented
Happydba, Max Vernon: I'm glad that you are not confused by the existing terminology. But lots of others are.
Too many people look at an execution plan and then say "yeah, but that's only the estimated plan, the actual plan might be different".
Too many people think creating an "estimated" plan is a different process from creating an "actual" plan (whereas in reality the only difference is that the "actual" plan is made by adding data collected at run time).
And this confusion stems from using different adjectives in front of "execution plan", which make it SOUND as if they are different plans.Joe Obbish: I don't know what "PEO" means. What your repro shows is the result of variable inlining, which only happens for a statement-level recompile. This happens before the optimizer even starts. The query gets internally changed to:
SELECT * FROM master..spt_values
WHERE @dummy = 1UNION ALL
SELECT * FROM master..spt_values
WHERE @dummy = 0
OPTION (RECOMPILE)And not that this happens when the statement executes. So at execution time the optimizer will first compile the batch (resulting in the same execution plan as you'd get when you hit the "get estimated plan" button), then execution starts, then before this statement starts a NEW plan is compiled, now with the variable inlined which allows a branch to be removed from the plan. And this plan is then later enriched with run-time statistics and returned to the client.
As already mentioned in the blog, recompiles can cause a new plan to be created. That new plan can look different from what was compiled before. But it's not a different type of plan.
-
happydba
commented
I don’t think I’ve ever been confused about the “estimated” versus “actual” execution plans.
It’s not just the addition of statistics that makes the actual plan the actual plan. Sometimes there could be a mismatch between the estimated versus actual plans (as you said in your post, due to recompilation), so I always *run* problematic queries to get the *actual* plan, and not just for its statistics. The shape might be different, which can be part of what’s causing a performance issue.
Yes, the “estimated” plan is technically A “actual” plan, but it’s not THE actual plan that I want to analyze. I want to make sure I’m looking at the plan the server actually used to run the query on my screen.
I think trying to rename/reaffirm that “estimated” execution plans are in fact “actual” execution plans might cause more confusion, because the new names wouldn’t hint that the plan could change when the query is run – whereas the current “estimated” and “actual” naming convention does.
I think the mishmash of [cached/precompiled and therefore potentially bad] estimated and actual run-time statistics on the estimated AND ALSO actual plans causes more confusion than the terminology of what the plan *name* is. That’s what is a real issue for me.
-
Max Vernon
commented
What problem are you trying to solve by changing the name of the Actual Execution Plan? I don't understand how Estimated Execution Plan could ever be confused with Actual Execution Plan. If you want to make a real change, call them Actual or Estimated, as appropriate, *everywhere*. Runtime Execution Plan would be far better than "Plus", which IMO, will lead to confusion about what "Plus" means. It sounds like obfuscation of the real intention if you append "Plus".
I wrote a blog post on the problems, and a better naming solution at https://www.sqlserverscience.com/documentation/on-nomenclature-and-execution-plan-plus/
-
Joe Obbish
commented
"Actual plan" seems to fit better than "execution plan plus" when PEO is in play:
DECLARE @dummy INT = 1;
SELECT * FROM master..spt_values
WHERE @dummy = 1UNION ALL
SELECT * FROM master..spt_values
WHERE @dummy = 0
OPTION (RECOMPILE);