SSMS 2016 New Feature – Compare Query Execution Plans

Management Studio in SQL Server 2016 has introduced a great new feature: Compare Showplan.

Frequently we can find that a query runs faster on one SQL Server instance than another and it can be quite puzzling as to why when on the surface the servers and software appear to be configured identically. In some cases we may find that one server has more CPU or memory resource, or faster disks or logical volumes with more disks in a RAID volume, but differences will also often come down to database version (or compatability version) and optimizer differences.

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.

Query tuning, optimisation and interpretation of query execution plans is a complex topic. If you would like some help on tackling SQL Server performance problems PTR offers a consultancy service which can be blended with training to help you on your way, alternatively why not take a look at our SQL Server Performance & Tuning course which is offered as a public scheduled course or can be offered as a bespoke course tailored to your specific needs.