When you are performance tuning code, you will frequently examine the execution plans to see what is happening. As you change the code, you will look at the execution plan again. However, comparing all the changes is troublesome. Fortunately, comparing query plans has become easy in SSMS (starting in 2016). Just save off the first (original) plan (using the .sqlplan extension). When you have some changes that you want to compare, get the actual execution plan. Then right-click on it and select “Compare Showplan”. Finally, select the saved plan.
After you have selected the first plan, both plans will open up in a new tab. The Properties tab will have properties for both execution plans:
Notice that various options have a colored non-equals icon. Here you can quickly see the various values that are different between the two execution plans.
At the bottom of the execution plans is a Showplan Analysis window. This window has color-coded keys for various sections of the plan:
If there are multiple statements involved in the query, you can select which one you want to compare in the Multi Statement tab:
This post is for day twenty-seven of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.