Compare Execution Plans in SQL Server 2016

By Samir Behara on April 12, 2016 • ( 2 )

With technology evolving this fast, I hate that we still have to deal with manually comparing execution plans. Things get worse when we have big plans. While working on a SQL Performance issue, it is often required to compare execution plans, before and after making a change. Wouldn’t it be nice to have a tool which would compare execution plans side-by-side and automatically present to you the similarities/differences?

In this article, we will look at the new Execution Plan Comparison Tool in SQL Server 2016 and how its integration into Management Studio makes troubleshooting performance issues so much easier.

Problem Statement –

Analyzing the execution plan for a TSQL query provides an accurate insight on how the SQL Server engine has processed the query. It provides lot of valuable information like how to retrieve data from the tables, are there available indexes, which indexes to use, which types of joins to use and more. Hence if we can compare two execution plans, before and after, it will help to exactly identify the performance bottleneck.

Currently we have to manually compare the execution plans and spot out the differences. Not only it is cumbersome but also it is difficult and time taking to visually find out the differences. For larger queries, with bigger execution plans, life becomes more tough!

When do you need to compare Execution Plans?

Think about one of the below scenarios and how many times have you come across it —

A query runs fine in DEV/QA environment but takes more time to execute in PROD.
A query has slowed down considerably in PROD and no changes have been made to it in recent past.
Query Performance has slowed down after doing a server/database upgrade.
You have fine tuned a TSQL query ( be it by rewriting the query with apt table joins or by making index changes) and want to explain the performance gain.

SQL Server 2016 introduces a ‘Compare Showplan‘ feature which allows side-by-side comparison of two execution plans & will be helpful when working in troubleshooting performance issues.
It is an Offline Compare, so you need not be connected to any SQL Server instance.

How to compare Execution Plans in SSMS?

Let us try to understand how it works with a practical example –

I have a query which we will execute against the AdventureWorks2016CTP3 database –

SELECT e.BusinessEntityID FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.ModifiedDate = e.ModifiedDate
WHERE p.EmailPromotion = 1

Below is the Execution Plan generated for the query —

As you can see, we are doing an Index Scan operation on the Person table, and to fine tune the query, we will need to create the below Index —

USE [AdventureWorks2016CTP3]
GO
CREATE NONCLUSTERED INDEX [idx_Person_Email_Date]
ON [Person].[Person] ([EmailPromotion],[ModifiedDate])
GO

Below is the Execution Plan generated after creating the index —

We were successfully able to convert the Index Scan operation on Person table to an Index Seek operation.

To leverage the comparison tool, you will need to open the 1st plan in SSMS. Now right click and you will see the new option – Compare Showplan.

Click on this option and select the 2nd plan you want to compare to.

You will now see both the execution plan side by side in the same window.

Part of the plan that are same in both the plans, are highlighted in the same color (in this case purple)

You can also open the dual properties window and view the results side by side, by selecting any particular operator. Here we can view the different properties of these 2 nodes like – Actual Number of Rows, Estimated Operator Cost, Number of Rows Read etc. These stats are very helpful in understanding the difference in query performance.

You can also choose the SELECT operator of both execution plans and look at the global properties to get beneficial information like Compile Time, Estimated Subtree Cost, Missing Indexes and more.

There is also an option to view the execution plan comparison side by side vs one on top & other on the bottom. This can be achieved by selecting the ‘Toggle Splitter Orientation‘ option –

Conclusion –

Execution Plan Comparison tool is a great addition to SQL Server Management Studio. We no longer need to manually compare those big execution plans, neither depend upon 3rd party tools.