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

ComparePlan1

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 )

ComparePlan2

As, highlighted, you can click on compare show plan and choose the one we saved as 1.sqlplan and here you go

ComparePlan3.PNG

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s