SQL Server Management Studio (SSMS) 2016 - Compare Execution Plans
SSMS 2016 New Feature – Compare Query Execution Plans
Management Studio in SQL Server 2016 has introduced a great new feature: Compare Showplan.
The new Compare Showplan tool is available by right clicking on a query execution plan in SSMS:
On selection of the Compare Showplan option a file manager dialog will open enabling you to navigate to a saved query execution plan. You can save a query execution plan to a .sqlplan file by right clicking on the plan and selecting Save Execution Plan As.
In the following example we compare a SQL Server 2008 execution plan with a SQL Server 2016 execution plan. Both queries are based on the AdvnetureWorks database. (the 2008 version and the 2016 version).
When the Showplan Comparison tab is initially displayed the SELECT operator is selected and the Properties window shows the execution plan properties for the SELECT operation (overall query). The first thing we can see is that the Cardinality Estimation Model Version (second value from the top) is different for the two plans: 130 for the SQL Server 2016 optimizer and 70 for the SQL Server 2008 Optimizer.
In addition to the optimizer version being different the compile timings are different and some of the estimated values are different. For example the Estimated Number of Rows is 2141.27 for the SQL Server 2016 plan and 3806 for the SQL Server 2008 plan.
Each physical operation in the execution plan can be compared by clicking on it. The following screen shot shows the comparison for a hash join seen in each plan:
In this particular example we can see that although some of the estimations are different they are very similar and the actual execution plans are identical.
In the next example the AdventureWorks2008 database has had the database compatability version changed from 2008 to 2014.
We can now see that the Estimated Number of Rows values are identical. The cardinality estimator was updated in SQL Server 2014 so that is not entirely surpising (although you might think it odd that the older SQL Server 2008 optimizer estinated the correct number of rows and the new improved optimizer estimated 2141.27!).
Don’t be surprised either that some of the compile times vary. These will often be higher the first time a query is executed and actual compile times can also vary depending upon load on the SQL Server instance. Try running the queries several times and comparing the plans and you will see this.
Comparing Different SELECT Statement styles
Sometimes we want to compare the plans for different query styles and see where the main cost and resource use differences are. The Compare Showplan option is perfect for this too.
You can save an execution plan at any time and open it later in management Studio. Then open the comparison tool and open a second saved plan file containing the alternative form of SELECT statement. The following example shows a simple join plan versus a correlated subquery to retrieve columns from two tables:
Comparing the costs for the overall query we can see that the second query (the one with two nested correlated subqueries!) is cheaper. The subtree cost is almost 1.2 lower and the memory requirements are lower too.
Great for SQL Query Tuning & Troubleshooting
This comparison tool is a really useful addition when troubleshooting performance problems. You can use SSMS 2016 to manage older SQL Server instances, or just to open saved execution plans.