SQL SERVER 2016 – Comparing Execution Plans

New tools bring new capabilities and sometimes these are ingrained deep into the product that we can easily miss them out. And SQL Server 2016 is no different. After installing the CTP, I was pleasantly surprised to see this addition to help our DBA’s who are doing performance tuning and query tuning activities.

So what am I talking about? Think of a scenario wherein you are doing some sort of testing in your QA environment. You seem to be troubleshooting a production scenario and found that very scenario to be working very well in your test environment. Though you have taken the data from the production database to the test QA server less than a month back, you are still baffled with the question – why this difference?

The next requirement everyone looks at is to start comparing the execution plans between your production and test environment for the same query. Some of the execution plans on a production server can run to pages sometimes and are difficult to find difference visually.

SQL Server 2016 – Plan comparison

If you have the execution plan from your production environment, then go ahead and open it in SQL Server 2016 Management Studio and right click to get the following option:

Solarwinds

SQL SERVER 2016 - Comparing Execution Plans compare-plan-00

You can see that an interesting addition. The “Compare Showplan” needs to be selected. Now, select the plan that was generated from your test environment. This will make bring both the execution plans on a side-by-side view as shown below. Look at the Purple color on the nodes.

SQL SERVER 2016 - Comparing Execution Plans compare-plan-01

This represents the fact that both the nodes are same but have some differences in some properties. Now it is upto us to right click and bring the properties tab.

SQL SERVER 2016 - Comparing Execution Plans compare-plan-02

Here we can go ahead and compare each of the node properties to find where the discrimination between the plans are. This is awesome capabilities added in the new version.

I am sure you will find this feature of great use in query tuning exercises in your environments. Having said that, one thing I have seen interesting is, if you take a live “Actual Execution Plan” and then try to compare it with a saved .sqlplan file – it will come up with an error as shown below:SQL SERVER 2016 - Comparing Execution Plans compare-plan-03

Do let me know if you would use this feature and if you have used this feature – what is some of the enhancements you wished this comparison operator had? I would like to learn from you for sure.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Installing and Getting Started With Semantic Search
Next Post
Interview Question of the Week #047 – Logic To Find Weekdays Between Two Dates

Related Posts

2 Comments. Leave new

  • This feature looks like it could be really useful but when trying to compare plans from a complex procedure with many statements it only looks to do the highlighting on the first statement? The actual statement I want to compare is one of the last statements ran and has dependencies on previous statements so it is hard to run that code in isolation.

    Any idea if there is a way to get this working across all statements?

    Reply

Leave a Reply

Menu