On Nomenclature, and Execution Plan Plus
Nomenclature, or the science of using words to name things using a logical system, is important since accurate names portray with clarity what the thing does. Recently, there’s been a suggestion, posited by Erin Stellato and Hugo Kornelis in a detailed and well-thought-out blog post, to rename the Actual Execution Plan used by SQL Server1 to “Execution Plan Plus Runtime Statistics” or “Execution Plan Plus”, for short.
Arguably, many people, especially those new to looking at plans, simply refer to them as either plans or execution plans, when talking about both the estimated and actual plans. The terms “Actual” and “Estimated” are perhaps not the most explicit names possible, however they are widely used, and concise, which is why people understand what they imply. I can see why it might be worthwhile to rename the plans to something more understandable, however adding “Plus” to the name, and removing “Actual” does not accomplish that.
It might be worthwhile renaming the “Actual Execution Plan” to “Runtime Execution Plan”, and retaining the “Estimated Execution Plan” name for estimated plans. Live Query plans, that can be seen with statistics as they happen via the SSMS GUI, could be called, somewhat intuitively and perhaps unsurprisingly, “Live Query Plans”. There are many technical reasons why the names for actual and estimated plans should be differentiated; I believe adding “Plus” to the name is simply meaningless to most people, especially for folks that are new to SQL Server.
I don’t agree that the proposed names offer the clarity that Grant Fritchey claims. “Estimated Execution Plan”, “Runtime Execution Plan”, and “Live Execution Plan” make more sense, and confer much-needed clarity.
Clarity is defined by the Cambridge Dictionary as the quality of being clear and easy to understand. In that light, the “Estimated Execution Plan” shows what SQL Server estimates it will do when executing the given query. The word “estimated” clearly denotes this may not be what is actually decided-upon for the execution plan at runtime.
“Runtime Execution Plan” denotes that this plan is the one actually used by SQL Server when processing the given query. “Runtime” is concise, clear, and easy to understand for even the most novice SQL Server user.
In the name “Live Execution Plan”, the term “live” is very easily interpreted to mean “that which is currently happening”, as in the definition contained in the Cambridge Dictionary.
The problem I see with the existing name, “Actual Execution Plan”, is that the term “actual”, when heard in a spoken sentence, can easily be misinterpreted as if the term didn’t exist. For example, the spoken sentence “show me the actual execution plan” could be taken as “show me the execution plan”, since actual is not easily recognizable as a noun in that context. Adding “Plus Runtime Statistics” to the name does eliminate the possibility of confusion since it contains the term runtime, however “Execution Plan Plus Runtime Statistics” is wordier than it needs to be. This is confirmed by the desire to shorten the name to “Execution Plan Plus”. The name I am proposing, “Runtime Execution Plan,” places emphasis on the term runtime, which is unmistakable in its meaning.
Others have already discussed, ad nauseam, the important differences between estimated plans and runtime plans. Suffice it to say, it is important that there be a readily apparent differentiation between the plans generated as an estimate, and those used at runtime. Differences between estimated and runtime plans can be easily seen with this sample code showing one interesting feature of the SQL Server query engine, Parameter Embedding Optimization2:
DECLARE @dummy int = 1;
SELECT * FROM master..spt_values
WHERE @dummy = 1
SELECT * FROM master..spt_values
WHERE @dummy = 0
The code above generates the following Estimated Execution Plan:
The Runtime Execution Plan for that query:
While they’re at it, perhaps Microsoft should consider renaming Extended Events, Profiler Plus. 😃
1 – The Actual Execution Plan conveys details of the work performed by SQL Server while running the T-SQL code declared in the query passed to it. It’s named the “actual” execution plan since it’s the plan that was actually used during execution. SQL Server doesn’t actually run the execution plan, it’s merely a graphical or textual representation of the steps taken by SQL Server encapsulated in an easy to understand format. I use the term “Actual” here to denote that the plan is what was actually used; another important aspect of that name is in the distinction between estimated rows, estimated rebinds and rewinds, estimated I/O costs, versus the actual, recorded values for number of rows read, rebinds, rewinds, etc.
2 – Thanks, Joe, for that succinct example!