Simple post today: How to compare two plans in SSMS.

This task is a task that is often done by a DBA or developer who is wanting to compare changes of an index or perhaps even comparing queries between DEV and PROD.

As an example, we take the following AdventureWorks2012 query:

SELECT DISTINCT City
FROM Person.Address
WHERE ModifiedDate = ‘2007-05-01’

And save the query plan as a .SQLPLAN file.

Having noted the KEY LOOKUP operator, I know this query can be improved with by adding City to the ncl_demo index as an included column.

For fun, I also ran the plan through my query plan website: HowsMyPlan.com and it noted the same concern.

After adding the index, here’s the new result:

Let’s say you want to compare these two plans and send the results to your team for training or whatever other purpose.

SSMS (SQL Server Management Studio) allows you to compare query plans with a simple right click context menu.

Clicking on “Compare Showplan” opens a file dialog which will allow you to choose a previously saved .SQLPLAN file.

Once selected, the “Showplan Comparison” tab will open with a top and bottom plan and details listed side by side. Clicking the operators in the graphical plan will change the details list and allow you to easily compare the data between each plan.

I hope you found this simple post helpful. Have a great weekend and be sure to check out HowsMyPlan.com

Also feel free to add me on LinkedIn and Twitter.