As technology evolves, new tools and capabilities are introduced to enhance our experience and productivity. SQL Server 2016 is no exception. In this article, we will explore a powerful feature that can greatly assist DBAs in performance tuning and query optimization activities.

Imagine a scenario where you are troubleshooting a production issue and find that the same scenario is working perfectly in your test environment. You have recently copied the production data to the test QA server, but you are still puzzled by the differences in performance. The next logical step is to compare the execution plans between the production and test environments for the same query.

In SQL Server 2016, there is a new feature called “Compare Showplan” that simplifies this process. To use this feature, simply open the execution plan from your production environment in SQL Server 2016 Management Studio. Right-click on the plan and select “Compare Showplan”. Then, select the plan generated from your test environment.

The execution plans will be displayed side-by-side, with nodes highlighted in purple to indicate differences in properties. By right-clicking on a node and accessing the properties tab, you can compare the properties of each node to identify the variations between the plans.

This feature is incredibly useful in query tuning exercises, as it allows you to easily identify discrepancies between execution plans and pinpoint areas for optimization. However, it is important to note that comparing a live “Actual Execution Plan” with a saved .sqlplan file may result in an error.

Have you used this feature in SQL Server 2016? If so, what enhancements would you like to see in the comparison operator? Share your experiences and suggestions in the comments below. Let’s learn from each other and make the most of this powerful tool.