One feature that was added in the 2016 version of SSMS that hasn’t received a lot of attention, is the ability to compare execution plans.
There’s two ways of doing this, from Query Store and from saved files.
Let’s start with Query Store, and I’m going to use a demo database that I’ve been working on for a few months – Interstellar Transport (IST). I’ve got a stored procedure in there that has a terrible parameter sniffing problem (intentionally). I’m going to run it a few times with one parameter value, then run it a few more times with another parameter value, remove the plan from cache and repeat the executions in the reverse order.
With that done, the query should show up in the ‘Queries with High Variance’ report (SQL 2017)
The query has the two expected plans, and they are quite different from each other.
I can click on the points on the graph individually to see the plans, but comparing the plans in that way is difficult and requires that I make notes somewhere else. What I can do instead is select two different points on the graph and chose the ‘compare plans’ option.
This brings up a window where the two plans are displayed one above the other, and areas in the plan which are similar are highlighted.
Select an operator and pull up the properties, and the properties of the operator from both plans are shown, with the differences highlighted.
This isn’t the only way to compare query plans. The next post will show how it can be done without using Query Store at all.