Compare Query plan using SSMS

Recently Microsoft released SSMS 17.0. This release of SSMS supports SQL Server 2017 for both running on Windows and Linux. In this release Microsoft has added new features as well as improved some existing features. One of them is to Compare Query plan.

You can download latest SSMS 17.0 here

This is helpful where you have previously saved plan and after new deployment your query is taking more time to run and you wanted to check what’s gone wrong in the query. During development also you can compare your queries to find which one you should use.

Note: You need one saved plan in order to compare.

Let’s run the below query with include actual execution plan and saved the plan to compare.

SELECT soh.[SalesOrderID], soh.[OrderDate], soh.[DueDate], soh.[ShipDate],soh.[CustomerID]
FROM [Sales].[SalesOrderHeader] soh
INNER JOIN [Sales].[SalesOrderDetail] sod on soh.[SalesOrderID] = sod.[SalesOrderID]
where soh.[SalesOrderID] >50000

Plan1

 

Now run below query with include actual execution plan where I have changed the parameter value for SalesOrderID from 50000 to 70000.

SELECT soh.[SalesOrderID], soh.[OrderDate], soh.[DueDate], soh.[ShipDate],soh.[CustomerID]
FROM [Sales].[SalesOrderHeader] soh
INNER JOIN [Sales].[SalesOrderDetail] sod on soh.[SalesOrderID] = sod.[SalesOrderID]
where soh.[SalesOrderID] >70000

query2

   

Now to compare your current query plan with previously saved plan, right click at Execution plan pane and select Compare showplan. It will ask you to select a saved plan with which you wanted to compare your current plan.

query2

Upon selecting compare showplan it will show you plan comparison as above. Mainly 3 section it will show you.

Section one will you show you graphical plan comparison.

Section two has three tabs.

  • Statement options: allow you to find similar operations
  • Multi Statement: If you have multiple queries, here you can select for query you wanted to compare plan. Only one statement from each plan can be selected.
  • Scenarios: This will give you some more insights of both the queries. Example: If any specific trace flag used for one query which is not used for other or if any difference in estimated number of rows etc.

Section three is the properties window. Here you can see each what which attributes are not matching and this is highlighted as not equal to ( not equal to).

There is one more new feature Analyze Actual Execution plan also added to SSMS. Not sure why I can’t able to see this feature in my SSMS, I am working on it and will be writing about this new feature very soon.

Many studies have shown that even a slight decrease in body weight reduces the risk of cardiovascular and https://phenterminehealth.com complications.

 

   

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

One Comment on “Compare Query plan using SSMS”

  1. “There is one more new feature Analyze Actual Execution plan also added to SSMS. Not sure why I can’t able to see this feature in my SSMS, I am working on it and will be writing about this new feature very soon.”

    Solution: Save your actual execution plan, and then open the .sqlplan file

Leave a Reply

Your email address will not be published.