Yesterday, Greg Gonzalez (blog | twitter) announced a beta project for SQL Sentry's latest offering: Plan Explorer.  I have been playing with the early versions of the beta, and I can tell you that DBAs should be very excited about having this tool in their arsenal.  This is a free companion to SQL Server Management Studio that greatly enhances your ability to read execution plans and find the most expensive operators by CPU, I/O, or both.  And yes, you read that right: FREE.

Greg published a post yesterday that goes into great detail about Plan Explorer.  I wanted to complement that with a few of my own thoughts on the tool.

The first plan I decided to evaluate was the plan you get when calling Adam Machanic's (blog | twitter) sp_WhoIsActive procedure (a great free tool in its own right).  I chose this procedure not because I am skeptical of its performance or am looking to improve the plan, but simply because I knew it would be a complex plan that would be difficult to even browse in SSMS, and that any of you could download the procedure and have it ready to go without any custom schema or other setup.

As expected, here is what it looks like in SSMS, zoomed to fit.  Note that this is just the very first plan (of about 20 – it's difficult to tell how many plans you're dealing with when using actuals in SSMS, since they're all separate and scrolling is not the smoothest):

Surely you'll agree that that is pretty ugly, and if you've dealt with largish plans in SSMS before, you'll know how cumbersome it will be to navigate around that plan once you've zoomed in enough so that the operator nodes will be readable.

The way Plan Explorer arranges this particular plan makes it much easier to spot the most expensive operators (since they will be highlighted in red or orange).  So even when zoomed to fit, you can immediately identify the areas on the plan where you need to focus.  The horizontal space in this blog post doesn't really give it justice, since I had to scrunch the image down.  But click to embiggen and you will see how obvious the expensive nodes become:

(So remember when comparing this visual plan to the SSMS plan above, that within this post, the Plan Explorer plan is at half size, and at 100% the red and orange would stick out a lot better.)

Now that I know which areas to focus on, I can zoom in.  Here is the SSMS view of the focus area:

 And here is the Plan Explorer view of the same segment:

What I like better about the latter is that the costs are highlighted at the top as opposed to the bottom.  This way your eyes are always looking in the same spot, instead of bouncing vertically from node to node because the location of the cost operator depends on the preceding text.  I also like that the row counts going from node to node are indicated above the arrows.  Did you notice that there is a tool tip for these row counts as well?  If you hover your mouse over a number, you will see additional information, such as actual and estimated rows and row size:

In SSMS, all you get visually is an arrow thickness to indicate larger row counts, but no number.  This arrow thickness does not take row *size* into account, and that visual indicator seems to escape a lot of people anyway.  The SSMS tooltip has the same information as above, but it is missing the *actual* data size (and I'm not sure why a figure for "number of rows" wouldn't always be rounded to the nearest integer):

Now, another thing you can do in Plan Explorer is to change the costing mechanism.  By default, it is CPU + I/O, but you can change it to just CPU or just I/O, depending on your bottlenecks in this query (or in different nodes within this query).  You can right-click anywhere in the plan, and you will see this context menu:

For this particular plan, if I change the cost criteria to I/O, I see that my highest costs are still spread between the two sort operators:

But if I change to CPU, I need to zoom out and I can quickly see that my CPU costs are really elsewhere (and if my query is CPU bound, I'll know that this is where I really need to spend my time).  In the following screen shot you can see the above operators on the left, and their CPU costs are zero, but there are some more expensive operators to the right which had combined CPU and I/O that didn't weigh enough to become an issue (and would never have been exposed by SSMS).  Click to embiggen:

This is still a multi-step operation, but one that isn't even possible using SSMS, unless you wanted to take screen shots of every single node's tooltip, and compare the estimated CPU and I/O costs in Photoshop.

I don't think it can be stressed enough: this tool is FREE.  So what are you waiting for?  Go download the beta and get some better visibility into your plans! 

Downloads:

.NET 4.0 (prerequisite)

SQL Sentry Plan Explorer (x64)

SQL Sentry Plan Explorer x86

SQL Sentry Plan Explorer Documentation (.doc)

SQL Sentry has also created a forum for discussion and support of Plan Explorer:

http://forum.sqlsentry.net/forum12-sql-sentry-plan-explorer.aspx