Comparing Execution Plans In SQL Server

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 the 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 to different execution plans? It makes it easy not only to visualize the differences, but it also shows you detail 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.
Comparing Execution Plans
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 a great tool to use prior to migrations, and as an added bonus, you have the ability to 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 check lists.
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.
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.
Comparing Execution Plans
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 As you hover over each node, you can dive into the properties and numbers on the lefthand side by default.
Comparing Execution Plans
The properties allow you to clearly see the cost and actual row count difference between each node in each plan.
Comparing Execution Plans
It is important to examine each of the places where the plans are different. I’ve run into situations where two plans had the exact 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.