Often we wonder and feel a need to compare two execution plans, usually we do it by opening these plans in two separate management studio or tabs on two screens (thanks to my company I always has two monitors).
With SSMS 2017 release, a new feature “Compare Plan” will allow developers/tuners to compare execution plans inside the management studio. Developers will have the ability to provide a side-by-side comparison of two execution plans. It will make administrators, developers’ life a lot easier to find similarities and variances on each plan.
This is my favorite enhancement that accompany in troubleshooting issues such as understanding the impact of rewriting queries or observing query plan behavior with small to drastic changes, like index, hints may influence plan generation. Let me show how you can do it in simple couple of steps. I will be using our favorite AdventureWorks. My focus how to compare two plans of two pretty much same queries.
How to do it ?
Fire off following sql query and make sure that you clicked on “Include Actual Execution Plan” or use shortcut ctrl+m before you run it
Select Top 1000 * from sales.SalesOrderHeader h inner join sales.SalesOrderDetail d on h.SalesOrderID = d.SalesOrderID
Where h.SalesOrderID in (Select Top 100 salesorderid from sales.SalesOrderHeader )
Click on execution plan tab and save this plan to disk lets say with name 1.sqlplan
Fire off the 2nd query which will bring 1 addition records and with tweaking of in clause to EXISTS.
Select Top 1001 * from sales.SalesOrderHeader h inner join sales.SalesOrderDetail d on h.SalesOrderID = d.SalesOrderID
Where EXISTS (Select Top 100 salesorderid from sales.SalesOrderHeader )
As, highlighted, you can click on compare show plan and choose the one we saved as 1.sqlplan and here you go
You can see and compare different plan attribute on one screen and can make assessment of your code changes. Many of us still relying on SQL Sentry plan explorer which I still believe top of line tool but this things get really handy on production where you may not have SQL Sentry installed.
adrshen 