This post is part of the series 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the series!
The CTP3 of SQL Server 2016 has just been announced, so it's time to look at the new features this version brings. As covered in previous articles, the SQL Server engine has evolved with many improvements. Today we'll see that SQL Server Management Studio has not been left behind either. A long-requested feature has finally arrived: the ability to compare execution plans.
When running a query, SQL Server creates an execution plan that lists the operations needed to produce the result. This plan can vary based on several factors: the query itself, available indexes, statistics, the version of SQL Server, and more.
Being able to compare two execution plans is often useful for spotting differences and understanding what causes a performance gain or loss for a given query.
#How does it work?
The first step is to save two execution plan files:
We now have 2 .sqlplan files:
Then, you can open one of the execution plans in SQL Server Management Studio and click on "Compare Showplan":
You now have 2 execution plans side by side:
You can even toggle the layout 😉
#Points of interest
There are two Property Grids (one per execution plan), making it easy to compare the details of each element.
Parts of the execution plan that perform the same operation are highlighted in the same color. This makes it easier to spot similarities and differences between the two plans, especially for large or complex ones.
Comparison works on offline execution plans (.sqlplan files), so you can compare plans captured at different times or from different servers, including across SQL Server versions. For example, you can compare the execution plan of a query run on SQL Server 2012 with one from SQL Server 2016.
#Conclusion
This feature is a welcome addition for anyone who regularly optimizes SQL queries, filling a long-standing gap in SQL Server's tooling.
Do you have a question or a suggestion about this post? Contact me!
