Have you ever worked with a query plan that was so large and unwieldy that it was next to impossible to make any sense of it? It can be a frustrating endeavor, for sure. Plan Explorer has always done some things that can help with these plans, such as color-scaling costs and vertically compressing subtrees. Even so, when you’re zoomed out to 10% and still only fitting a quarter of the plan on screen, color-scaling won’t buy you much.
The new version of Plan Explorer PRO 2.5 is focused on making these big plans easier to manage, and it does so using a variety of techniques that you have been asking for, which I will cover below.
Operation Filtering
The quickest way to shrink most plans is to apply a cost filter using the Filter slider. This is just as it sounds – if an operator’s (aka, iterator’s) cost is less than the specified filter value, it will disappear from view. There is a caveat: any operators “upstream” in the same subtree as an unfiltered operator (one that exceeds the filter value) will still be shown, in an effort to avoid changing the meaning of the plan too dramatically. The net effect is that as you increase the filter value, op nodes will start dropping off at the leaf levels, working up towards the root node.
Here’s a nice example using a plan Jonathan Kehayias (blog | twitter) shared with me a while back, aptly named “ReallyBadPlan.sqlplan.” Here is the original, with no filter:
… and with a .1% filter applied:
…and with a 2.3% filter applied:
As you can see, with big plans a little filtering can go a long way. The zoom level is reduced significantly, and the whole (filtered) plan is in view. The more op nodes, the lower the average cost per node, so this makes sense.
Collapsible Subtrees
Another way to quickly reduce a plan is by collapsing subtrees that aren’t of interest. You do this via the new expander element on the head node of each subtree:
The expander’s primary function here is to collapse, but “collapser” just doesn’t have the same ring to it 😉
Multiple Layout Modes
Some plan types simply don’t fit well on screen, and this is where using a different layout mode can be helpful. It’ll be easiest to illustrate this with a couple of common examples.
First, let me say that these “non-standard” plan layouts may be unsettling for some. If for whatever reason you are only willing or able to process plans using the traditional layout, my recommendation is this: don’t use this feature, and skip to the next section now, lest you may have these images permanently seared into your brain!
Stair Step Plans
First up is a fairly extreme example of a “stair step” type plan, which I see most often with data warehouse queries against star schemas:
The plan is so vertically intensive that there is no way to get the entire thing in view and still have it be usable. By usable I mean in a state where I can clearly see the costs, colorization, line widths, operator glyphs, and other visual cues necessary for troubleshooting.
By changing to Alt-1 mode we can vertically compress this plan in dramatic fashion:
I haven’t applied any filters, or changed any other settings, and as you can see it’s already much more usable. The trick is being able to do a little neural rewiring such that the non-standard orientation of some of the nodes with respect to each other doesn’t interfere too much with your interpreting the plan.
UNION Query Plans
With UNION queries you effectively have multiple individual plans in all their glory stacked on top of each other. This can make them quite tall due to the potentially large subtrees off of the concatenation operators, as in this plan:
Bus mode can sometimes help with this, like so:
With Bus, subtrees can be centered and staggered, taking better advantage of the horizontal screen real estate, typically a less precious commodity on modern displays.
Be warned, not every layout mode will work well with every plan. For example, Bus mode applied to the “Really Bad Plan” above will create something far nastier and far less usable than the original. Every plan is different, so you may need to experiment to find which layout works best.
Rotation
As Paul White (blog | twitter) describes in his post Iterators, Query Plans, and Why They Run Backwards, plan execution order flows left-to-right, which is likely why Microsoft decided to put the root node on the left. He also covers how data flows in the opposite direction, right-to-left. So if you are concerned about I/O, you may commonly start on the right, as I do. Since this is opposite of normal reading direction for many of us, a mirrored view may be easier to process. This can be accomplished by clicking either rotate arrow twice to get to 180 degrees:
If you change to Centered mode and click the right rotation arrow once to 90 degrees, the plan starts to look like an org chart, which may work better for those who read top-to-bottom (or work in HR):
Dynamic Auto-fit
Your wrist will appreciate this feature – it automatically resizes new layouts to fit the screen, so you don’t have to continually change the zoom level after applying filters or otherwise changing the plan layout. (Astute readers may notice that this checkbox is shown in some of the shots and not others – that’s because it was added while I was writing this, and I didn’t feel like redoing a bunch of shots 😉
Persisting and Sharing Custom Layouts
Custom layouts are automatically persisted when saving a plan with Plan Explorer as a .queryanalysis or .pesession file. Note that manually repositioned nodes are not currently persisted, but we’re working on that.
Plan Explorer PRO users who don’t want to use the custom layout in a shared plan can hit the Defaults button to reset everything. Plan Explorer FREE users can view, but cannot modify, Plan Explorer PRO custom layouts via the Apply Embedded Layout Options checkbox at bottom left of the diagram:
Closing
There are probably as many different optimal layouts as there are big plans. Our goal with this release was to provide more control over how plans are presented, so you can view them in ways that work best for you. You’ll need to experiment to see which combinations of modes and settings work best for each plan. I’ve personally had a lot of fun playing around… even the really bad layouts can provide some entertainment value 😉
Kudos to the dev team led by Brooke Philpott (blog | twitter) and QA team led by Steve Wright (blog | twitter) for their great work here. Thanks also to those of you who have shared your ideas with us… this release is a direct result of that feedback.
Enjoy!
