In my previous post on execution plans we looked at what an execution plan is, how SQL Server creates them and why we might use them, now we’ll delve a little further.
Recap
We very briefly compared two SQL Server execution plans on two queries against the Stack Overflow 2010 database provided under cc-by-sa 4.0 license from Stack Exchange Data Dump to see which of the following logically equivalent statements was the most efficient (as before, all examples are executed against the database in compatibility level 130)
SELECT *
FROM dbo.Posts p
JOIN dbo.Users u
ON p.OwnerUserId = u.Id
WHERE u.DisplayName = 'John';
SELECT *
FROM dbo.Posts p
JOIN dbo.Users u
ON p.OwnerUserId = u.Id AND
u.DisplayName = 'John';
and we observed that SQL Server decided to execute them in exactly the same way so there was nothing to pick between them.
I neglected to mention almost everything about the execution plan as the intention was just to give a very quick overview on how we can use the execution plan to compare one query to another.
In this article I will look to go into more detail about what can be found in the execution plan.
Actual Plan vs Estimated
As I mentioned before, there is a difference between these concepts – they are the same thing however, while an estimated execution plan is what SQL Server compiles at the optimization stage, the actual plan is the same plan but is updated with various runtime information and can be displayed to the user after the query has finished executing.
Let’s use this query again and compare the estimated and actual plans to highlight the differences I am talking about, the database is running in compatibility level 130 on SQL Server 2022 on a VM with 8 cores and 46GB RAM.
SELECT *
FROM dbo.Posts p
JOIN dbo.Users u
ON p.OwnerUserId = u.Id
WHERE u.DisplayName = 'John';
The Estimated execution plan:
The actual execution plan:
How the Plan Flows
The execution starts at the top right and flows to the root node at the top left – think of this as a tree. Each node is known as an operator or an iterator and can be thought of as a program in its own right. Each operator takes data from the previous as an input (unless it is one of the leaf nodes) and performs some action on that data, passing the residual data to the next operator.
There are many great resources out there to describe what each operator does and I have linked to some useful resources at the end of this article.
Spot the Difference
The first difference to note is that there are some numbers against each operator in the actual execution plan that are not on the estimated execution plan.
The uppermost of the three shows the amount of overall execution time spent in this operator, so here SQL Server took 1.53 seconds to scan the posts table.
The next value is the actual number of rows the operator returned, the final value is the number of rows SQL Server estimated would be returned by this operator when the plan was compiled.
Given that two of these values (execution time and actual number of rows) are only known once the query is executed, we can see why they do not appear on the estimated execution plan.
Operator Properties
Each operator has a whole host of properties which give us various pieces of information about what SQL Server thought the operator would do and what it actually did. Some of this information can be seen in a tooltip (which for a tooltip, contains a lot of information!) if you hover over the operator with the mouse. This can be done on either the estimated or actual plan, the below screenshots are from the actual plan.
This tooltip is a “quick” overview of the operator properties (I have hovered over the Clustered Index scan on the users table) even more information can be viewed by right clicking the operator and selecting properties (or pressing F4 on the keyboard):
What do the Properties Tell Us?
I won’t labour on all of the properties – as you can probably tell from the sheer volume of them, that is yet another topic in itself, however, I will look at a few properties on the tooltip that are often useful and give a brief explanation as to what they are, highlighted below.
Something to mention here is that this wording has changed over the versions of SQL Server Management Studio, for clarity, I am using version 22.
The order of the properties in the tooltip isn’t very logical (the great Brent Ozar often provides some amusing commentary on this in his training videos) however, I will walk through them in the order they are in the screenshot.
Actual Number of Rows Read
The number of rows that were, well…read. In our case, this is a clustered index scan – it is reading all of the rows in the table so this number will match the number of rows in the table.
Actual Number of Rows for All Executions
Subtly different to the above, this is the number of rows that were output by the operator. We can see lower down there is a predicate (DisplayName = ‘John’) so this number refers to how many rows matched the predicate. Furthermore, we can now say 299,398 rows were read and only 932 were output by the operator – that’s 298,466 rows that were needlessly read.
Estimated Number of Executions
The number of times SQL Server thought the operator would be executed when it compiled the plan. This number is often more than 1 on the inner side of a nested loop join.
[Actual] Number of executions
The number of times the operator actually executed. In our case this is 1 – the operator scanned the table once. Again, you will likely see different numbers to this on operators that have been executed on the inner side of a nested loops join but here it’s 1. NB – the word actual is missing from the tooltip here where it is included for other actual metrics.
Estimated Number of Rows For All Executions
The sibling of actual number of rows property above. SQL Server thought 892 rows would match the predicate (DisplayName = ‘John’) when it compiled the plan
Estimated Number of Rows Per Execution
How many rows SQL Server estimated would be returned per execution. Again, on index seeks, this could be >1 but here it is 892 as above.
Estimated Number of Rows to be Read
The sibling of Actual Number of Rows Read. SQL Server thought there would be 299399 rows in the table when it compiled the plan
Estimated Row Size
SQL Server’s estimate of the average size of each row in the table in Bytes
Predicate
In this case SQL Server has pushed the WHERE clause filter to the operator that is accessing the rows from the table – only the Johns will be passed to the next operator
Output List
The columns that will be returned to the next operator (the list is long, because we have used SELECT *)
Let’s look at what we see in the same operator of the estimated execution plan:
We can see a lot of the same things, however note all of the “actual” properties aren’t here – that’s because it’s an estimated plan, it’s the plan that was compiled, without the runtime information.
Data Arrows
The arrows show the path the data takes which always flows from right to left. The thickness of the arrows, confusingly shows represents different things depending on whether it is an actual or an estimated execution plan.
To explain this difference, I will use a slightly different query to before, one which just returns all the columns in the users table for any user called John:
SELECT *
FROM dbo.Users
WHERE DisplayName = 'John';
Firstly, on an estimated execution plan, the arrow thickness represents the estimated number of rows for all executions (i.e the number of rows the operator outputs)
For the actual plan, the line represents the number of rows read (i.e the number of rows that were processed by the operator but not necessarily output)
If you look carefully, you can see the line sizes are indeed different (though it can be difficult to distinguish the scale in SSMS sometimes in my opinion)
Furthermore, the line thickness differs by application – other execution plan renderers may differ from the above but I’ll leave that out and just stick with what the native application (SSMS) shows.
For this simple introduction, just remember – thicker line = more data.
Cost
You may have noticed references to “Cost” throughout the plan, cost is referenced:
- In the plan header as a cost of the query relative to the batch
- On each operator in a number of properties
“Cost” is a somewhat meaningless metric in terms of what it means in the real world. Legend has it that once upon a time 1 cost unit was equal to one second of runtime on a particular PC at Microsoft HQ but now it is somewhat abstract and doesn’t really relate to any real world metric.
I do find it helpful to remember though that under the hood in some way cost does relate to how much CPU and IO SQL Server estimates an operator will use and the units are comparable – a plan with a cost of 1 is probably going to use less CPU and IO resources than a query with a cost of 1000000. That said, I rarely rely on cost to do any real benchmarking and other than a quick comparison of plans, it’s not something I would ever fully base a decision on.
SQL Server uses a Cost Based Optimizer, meaning it will base plan choice on estimates of how much various plans will cost.
A final note on cost is something that I referred to earlier which is that the plan header shows the Cost Relative to Batch. What this means is that if we have a batch of two queries and Query A is costed as being 6 and Query B is costed as 4, Query A will show a cost relative to the batch of 60% and Query B will show a cost relative to the batch of 40%.
A further quirk here is that even on an actual plan, these percentages are still estimates and as I have already mentioned, estimates can be wrong so this is not often a very useful metric to base decisions on.
Estimates vs Actuals
Note that on some operators we see the estimates vs actuals differ and you may be wondering why. That is something I will talk about a lot on this blog as estimates vs actuals drift is at the heart of a lot of performance issues.
Conclusion
This article was intended to add more meat to the bones of what is an execution plan so we looked at the differences between estimated and actual plans and also looked at what information can be obtained from the execution plan.
References / Further Reading
Brent Ozar – What The Arrow Sizes In Query Plans Really Mean
dba.stackexchange -Actual Query Plan Estimated Cost and dm_exec_query_stats worker time do not gel
Erik Darling – Never Judge a Query by Its Cost
Erik Darling – Is It Time To Remove Costs From Query Plans In SQL Server?
Fabiano Amorim – Complete Showplan Operators
Kendra Little – Actual Number of Rows Read in SQL Server Execution Plans