Compare Execution Plans

Have you ever wanted to compare execution plans from SQL Server Management Studio? Well with the newer version of the software (SQL 2016 onward https://msdn.microsoft.com/en-us/library/mt238290.aspx) you can. Lets dive in.

Once you have an execution plan ready right click on it and you will see the show plan compare option.

compare

You will then need to navigate to the other execution plan that you want to compare. Ultimately they will then be “side by side” to allow for easy comparisons.

There are basically 3 windows to this, your top plan (blue box), bottom plan (orange box) and the options menu (yellow box).

compare1

Or you can “toggle split” to change the orientation of the windows

compare2

As you can see in the yellow box I selected highlight similar operations, I then use the properties menu to see a high level comparison of the SELECT operator.

compare3

Notice the Wait stats information too? I thought that was pretty cool.

If you are interested in a certain operator that you want to compare you need to select it within both query plans and go to the properties window. Here I look at the hash match operator.

hash

Then selecting the “highlight operators not matching similar segments” option you then will see where the execution plans differ. This was easy to see as the plan shape between the 2 plans was quite different.

shapes

This is a little feature that could prove quite handy.