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.

 

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