Refactoring and improving the performance of code is a regular occurrence in the working day of a developer. Being able to identify if a particular query refactoring has helped improve an execution plan can sometimes be difficult. To help us identify plan changes, SSMS 16.x and higher now offers the option to compare execution plans.

By saving the execution plan and the T-SQL of our initial query as a .sqlplan file, we can then run our redesigned query and compare the two plans. In the following screenshot we see how to initiate a plan comparison:

Upon activation, we must choose which .sqlplan file we would like to use for the comparison session. The two execution plans are loaded into a separate Compare Showplan tab in SSMS and we can evaluate how the plans differ or how they are similar. In the following screenshot we see a plan comparison where there are only slight differences between the plans:

The nodes of the execution plans in the preceding screenshot that are similar have a red background, while nodes that are different have a yellow background.

If we click the nodes inside one of the plans, the matching node in the comparison plan will be highlighted and we can then investigate how they are similar and how they differ.

Once we have chosen the node in our execution plan, we will be able to view the properties of the node we wish to compare, similar to the details shown as follows:

The Properties tab shows clearly which parts of the node are different. In the preceding screenshot we can ignore the lower inequality, which is stating the Node ID is different; this will occur wherever our query has a slightly changed plan. Of interest in this case is the Estimated Operator Cost property, which is showing a difference. This example is very simple and the differences are minimal, but we are able to identify differences in a very similar plan with a few simple clicks. This sort of support is invaluable and a huge time saver, especially where plans are larger and more complex.