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
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
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.
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).
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.
“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