I’ve watched several people recently go straight to XML when reading execution plans because they didn’t know about the execution plan properties in the first operator. Now, don’t get me wrong. If going straight to the XML is working for you, that’s fine. Keep doing it. I’m absolutely not questioning how anyone does things. I just want people to know that “hidden” information isn’t so much hidden as much as it’s not too obvious.
I’ve written before about the first operator in an execution plan (here, as well as here, and here, and even a problem with them here). I don’t have a whole lot to add to those posts. If you look around at other blog posts I’ve done on execution plans, I use the properties of the first operator quite a lot. These are the most common things I see people looking for when they go into the XML of an execution plan. That’s because the execution plan properties, the properties that define the plan, and that the optimizer used to create it, are extremely important.
If you want to go through the XML, no one is in your way. Have fun.
If you don’t want to go through the XML, just get in the habit of right clicking on that first operator and bringing up the properties window. The overwhelming majority of the time when I’m working with execution plans, my SSMS window looks like this:
Don’t worry about the details on the picture, the plan, or the actual properties. It’s just the properties window that matters. When I open a plan, because I have the properties window open by default, I always (with one exception) see the execution plan properties in that first operator. Right out of the gate. So I don’t have to then chase the XML.
The exception is when a plan gets capture using Trace or Extended Events. I linked to the problem above. For whatever reason, something about the plans captured using Trace & ExEvents results in a missing first operator. Then, even I go directly to the XML.
Again, I want to emphasize, I’m not telling you that going directly to the XML is in any way wrong, bad, or calls you into question as a moral person. I’m just saying, I hate looking at XML if I don’t have to. And, in this case, I don’t have to. Neither do you. So, the next time you want to see the Reason for Early Termination, statistics used in a plan, memory allocation, all that stuff, remember, the execution plan properties are your friend.