Latest SSMS Includes Comparison Tool

The latest release of the SQL Server Management Studio (SSMS) tool, CTP 2.4, from Microsoft now includes the ability to compare execution plans. This allows the user to compare two execution plans, which is helpful in troubleshooting performance issues, and is something people have been doing manually for many years.

So how do I use it?

Simply open SSMS, open a query execution plan file (.sqlplan) using File -> Open File, or drag a plan file to SSMS window. Once the file opens, right-click anywhere inside the tab (not necessarily on top of an operator) and select “Compare Showplan”. This works with any .sqlplan files you have, even from older versions of SQL Server. Also, this is an offline compare, so there’s no need to be connected to a SQL Server instance.

image

Choose the second query plan file that you would like to compare. The second file will open so that you can compare the plans, one on top and one on the bottom. You can toggle them side by side by right-clicking anywhere inside the tab, and selecting “Toggle Splitter Orientation”

image.

If not open yet, access the dual properties window by right clicking anywhere on either showplan, and selecting “Properties”.

image

And what does it show me?

When two showplans are compared, regions of the plan that do essentially the same thing are highlighted in the same color. Clicking on a colored region in one plan will center the other plan on the matching node in that plan. You can still compare unmatched regions of the showplans, but in that case you have to manually select the operators you want to compare.

Also, instead of the usual properties window, a dual property window grid displays the selected nodes side by side, for easier comparison.

Note that only nodes considered to change the shape of the plan are used to check if sections are equal. Therefore, there may be a node which is not colored in the middle of two nodes that are in the same subsection of the plan. The lack of color in this case implies that the nodes were not considered when checking if the sections are equal.

You can read more about this changes here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s