Comparing Execution Plans In SQL Server

Introduction to Execution Plan Comparison

When you run a query twice and get dramatically different run times, your first step can be to try to identify the differences in execution plans. For many, that means running two different queries (before & after) and splitting your screen in order to visually compare the plans. Did you know SQL Server Management Studio gives you the option to compare different execution plans? It makes it easy not only to visualize the differences, but also shows you detailed properties that allow you to dive into the numbers. This functionality was introduced with SQL Server 2016 and is also part of the functionality of the Query Store GUI shown below.

SQL Server Management Studio (SSMS) features

The SQL Server Management Studio comparison process allows you to compare any two .sqlplan files including those from old SQL Server versions. In the comparison windows, you will see the top plan is the first plan you open or have run and the lower will be the second plan chosen for comparison. As you can imagine this is a great tool to use before migrations, and as a bonus, you can compare the plans offline so no need to connect to a SQL Instance. This information can be very useful and should be added to your testing checklists.

Highlighting Similar Query Operators

When comparing the plans, Management Studio highlights similar query operators by shading them with the same color. The best place to start looking for differences is in the properties pane, which you can see on the right—where there’s a not equal sign (≠) you can easily see what is different in costing these two plans.

If you're new to SQL Server Management Studio (SSMS), check out Learn All About SQL Server Management Studio. 

Let’s see it in action.

How to compare execution plans?

To compare execution plans you need to first save one of the plans you wish to compare. To save the plan, just right-click in the execution plan area and choose "Save Execution Plan" as shown below in purple.

Once you have a plan saved, go ahead and run the plan you wish to compare to. After execution, keep that window open, then right-click and choose "Compare Showplan" and pick your saved plan. The below example is from docs.microsoft.com. As you hover over each node, you can dive into the properties and numbers on the left-hand side by default.

The properties allow you to see the cost and actual row count difference between each node in each plan.

Encouragement to explore and test

It is important to examine each of the places where the plans are different. I’ve run into situations where two plans had the same plan shape and only minor differences in memory grants, but the CPU consumption by each plan was vastly different. This is the kind of scenario where the properties comparison is invaluable. The old approach was to parse the XML of each plan to get the different memory grants.

This is just a quick reminder that SSMS gives us this functionality built in. I encourage you to take it for a test drive.