The first time you see a new execution plan that you’re examining to fix a performance problem, something broken, whatever, you should always start by looking at the first operator.
First Operator
The first operator is easily discerned (with an exception). It’s the very first thing you see in a graphical execution plan, at the top, on the left. It says SELECT in this case:
This is regardless of how you capture the execution plan (with an exception). Whether you’re looking at an execution plan from the plan cache, Query Store, or through SSMS, the execution plan, regardless of complexity, has this first operator. In this case, it says UPDATE:
If you get an execution plan plus runtime metrics (previously referred to as an “actual” execution plan), you’ll still see this first operator. Here, again, SELECT:
Why The First Operator
If you just look at the tool tip for the first operator, you’ll think I’m crazy for calling this out as your starting point:
However, if, like me, you live with the properties window open while you’re navigating execution plans, you’ll see a whole different set of data:
This massive list of information is why I suggest you start examining plans by looking at this operator. Here you find things like:
- Statistics used to compile the plan
- Reason for plan compilation termination, if any
- Memory used to compile the plan
- Compile time
- Parameter values used to compile the plan
- Parameter runtime values in the case of an execution plan plus runtime metrics
- SET options used to compile the plan
- Type of compilation; Full, Timeout, Trivial
- Missing indexes, there could be more than one
- For an execution plan plus runtime metrics, wait statistics
And more. There really are just tons of information about the plan within this first operator. Understanding of why a plan is problematic starts with understanding the choices made by the optimizer. The beginning of the list of those choices starts with the details of the first operator.
Except!
With everything there is an exception. SQL Server only has one kind of execution plan (more on that coming very soon). However, due to a bug in both Extended Events and Trace Events, the plans captured there, while identical in every other way to every other way of capturing an execution plan, the XML is malformed so the first operator doesn’t show in the graphical plan.
However, most of the data you’re interested in is still visible. However, you can only see it by looking at the XML of the execution plan.
I think Microsoft is working on this. They were actually only informed of it last year even though the problem is quite old. This doesn’t change the importance of the information where the first operator would be though.
Conclusion
After you’ve been working on a plan for a while, of course you don’t need to be going back to this operator over and over. However, that first time you’re looking at a plan, when you’re just starting to come to grips with it, start here. The first operator provides a long list of details, meta data, about that execution plan that helps you understand the plan.
Its a good habit to start looking, I am to stuck on the most expensive operator. As usual great article.
Looking at the highest cost operator is also useful. Just not as a starting point to understanding the whole plan. Thanks for the kind words.
Great information! I usually start with the most expensive operator, too. (Too bad about the NE Patriots…)
Ouch!
Always the first operator first in order to better understand the rest of the stuff I’ll be looking at. High cost operators are fine. Scans are also an indicator. Nothing is wrong per se, just trying to get the max amount of info.
Good stuff Grant!
To the commenters that say they start with just expensive operator, beware that this is based on estimated row counts, not actual. Even in an execution plan plus runtime statistics, the cost percentages are still estimates.
So at the very least, please check whether the estimate is correct or not, BEFORE zooming in on the highest cost operator. (And yes, all that only after looking at the first operator).
[…] Grant Fritchey reminds us to look at the first operation when viewing an execution plan: […]
Awesome post! I recently saw your presentation on SQLBits XVIII “Learn to Read Execution Plans” and it instantly became one of my favorite presentations of all time, extremely captivating, funny and full of valuable information. Thank you so much. I also want to ask you, what resource or set of resources would you recommend to someone who wants to dive into the dark side of execution plans. I often end up crying in the bathroom because I get lost in evil execution plans. Do you believe “SQL Server Execution Plans†book is a good starting point? Thank you so much!
Thanks for all the kind words.
So, I’m prejudiced, but yes, the 3rd edition of the book SQL Server Execution Plans is the single unique resource on execution plans. Also, make sure you check out Hugo Kornelis’ blog: https://sqlserverfast.com/blog/
That is another unique resource. Best of luck. Let me know if you have questions.