April updates to Plan Comparison Tool

  • Article

Cross post with https://aka.ms/sql_server_team

With the SQL Server Management Studio April update, we released a new update for the Plan Comparison Tool. We first released this tool as part of SSMS back in October (CTP2.4), with which you can do offline comparison of execution plans, from SQL Server 2005 to the latest and greatest. Learn more about how to start comparing your plans in the previous posts here and here.

So what has changed since last February?

We listened to the feedback about Showplan operator spacing, and made changes to the user experience even when you are viewing a single execution plan, which in turn improves plan comparison.

In SSMS 2014
clip_image001_thumb2

In SSMS 2016 RC – Feb Release

clip_image002_thumb1

In SSMS 2016 RC – April Release. Again thanks to all users that report issues and feedback on the product, as we’re only able to improve thanks to this prompt feedback!

clip_image0016_thumb1

Now to what is new and specific to the Plan Comparison Tool.

Same as before, when two showplans are compared, regions of the plan that do essentially the same thing are highlighted in the same color and boundary texture. But now you have a new Showplan Compare Options window to control some options. This opens by default when you compare plans, and will be extended in the future. So again, we need your feedback on what you need to see when comparing plans, that can be added in this contextual window.

image_thumb7

You can also enable the Options window by right-clicking anywhere in a compared plan, to bring up the following context menu.

image_thumb5

So what’s new in this window?

  • You can turn ON or OFF the highlighting of similar operations, as seen below, marked as 1.

    image_thumb12

  • You can turn ON or OFF highlighting of non-similar operators, below marked as 2.

    image_thumb15

  • You can also compare plans from different databases, like a production database and a test database. You can turn ON or OFF the ignoring of the Database name as seen below, marked as 3.image_thumb20

  • And clicking on any of the operators in the legend, jumps to that location in both plans (arrow below).

    image_thumb23

We also changed the highlighting of differences in the properties window, in a way that draws more attention to differences, as seen below. Each marked line means there is a difference between compared operators in both plans.

image_thumb19
All these and upcoming improvements would not be possible without user feedback, so keep it coming. Please use the comment section or better yet, the “Email Blog Author” on the right for that purpose – many thanks in advance for your feedback!

Pedro Lopes (@sqlpto) – Senior Program Manager