January 6, 2012 | SQL Server

Comparing multiple plans in SQL Sentry Plan Explorer

I’ve seen the question a few times now: “How do I compare two plans side-by-side in Plan Explorer?” Management Studio allows you to view multiple graphical plans that are visible simultaneously, either by generating plans for every statement in a batch, or by generating plans in separate query windows and then splitting the panes. However, at least in my estimation, a lot of the other metrics and properties are more valuable when performing comparisons. In SSMS this is tough, because you can only have one tooltip visible at a time (regardless of how many plans are visible). Same for the Properties panel – only a single plan or operator can populate this panel at one time. So even though you can compare high-level graphical details quickly, you will likely end up with multiple instances of SSMS to perform true side-by-side comparisons of the lower-level details.

In Plan Explorer (free download) it is very easy to compare a lot of the metrics side-by-side for two (or more) plans. Since Plan Explorer can generate estimated or actual plans for you, it’s as simple as entering multiple queries in the Command Text pane, and then generating an estimated or actual plan:

I recommend trying the Vertical Layout, so that the various plan-related tabs and the Statements Tree are a lot closer together (reducing mouse and eye movement):

Here is a quick comparison of the two default options (vertical on the left, horizontal on the right – click to enlarge):

(There is also a Custom Layout option if you wish to change from the default options.)

And you can still compare a lot of the other details about two different statements by looking at the other columns in the Statements Tree tab. For example, comparing costs, row counts and operation counts between estimated or actual plans can give you a much more accurate picture of the performance impact of different statements:

And you can switch between graphical plans quickly by using arrow keys or mouse clicks to switch between statements:

Since the other tabs are context sensitive, you can do this for any tab: simply highlight a tab (e.g. Top Operations) and, as you switch through the rows in the Statements Tree, the tab below will update accordingly.

For more complex plans, and depending on how much screen real estate you have, you may prefer to open two instances of Plan Explorer so that you can interact with the plans a little more independently. In this case you won’t be able to see percentages or other relative comparisons directly against one another, but for most eyeballing, it’s still better than what you get in Management Studio IMHO.

That all said, comparing two graphical plans side by side is definitely on the list for a future update; in the meantime, I hope the above is helpful.
 

3 comments on this post

    • Elliot - January 16, 2012, 11:45 PM

      Can you not just save the plans and open them independently, viewing them side by side?

    • AaronBertrand - January 16, 2012, 11:51 PM

      Elliot, yes, that's definitely an option that I mentioned toward the end of the post. Some people are arguing for a single app instance instead of multiple, and we're certainly keeping that functionality in mind.

    • Gullimeel - June 22, 2012, 8:51 PM

      Thats good. I have written a proc to do the same thing.But I am using pure sql so not able to get the memory grant and other actual plan related stuff. If i knew about this earlier. I would have not written the proc.
      BTW, I would like to suggest that while comparing or even if running a single statement. WaitTime(CPU and resource both),memory used value(memory grant) should be displayed along with CPU and IO details. Then you can provide the details of the Wait times of various wait types. This will be quite useful.
      I have done this in my proc using the Extended events to capture the wait_info event.
      if possible total latch duration and total lock duration could be added as well.I tried this as well but for big queries. This could generate huge data and thus dsiplaying it could be time consuming.
      Then Latch details could be displayed based on fileif,pageid so that if there are contentions these can be seen here.

Comments are closed.