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 take a look at the new features provided by this version. As we have seen in previous articles, the SQL Server engine has evolved and brings many new features. Today we will see that SQL Server Management Studio has not been left behind. Indeed a new feature requested for quite some time appeared: the comparison of execution plans.
When running a query, SQL Server creates an execution plan. An execution plan contains the list of operations to execute to obtain the result of the query. It can differ according to several criteria: different requests, available indexes, statistics, version of SQL Server, etc.
It is sometimes useful to be able to compare 2 execution plans to see the differences between them and thus to understand what can bring a gain or a loss of performance of a request.
#How does it work?
The first step is to record 2 shots of executions:
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 2 Property Grids (one per execution plan) which makes it easy to compare the detail of each element.
The parts of the execution plan doing the same thing are colored the same color. It is thus easier to see the similarities or the differences between the 2 execution plans, especially when they are big.
The comparison is done on offline execution plans (.sqlplan files). This makes it possible to compare execution plans taken at different times, or from different servers (including versions). It is thus possible to compare the execution plan of a query played on SQL Server 2012 with that executed on SQL Server 2016.
#Conclusion
This new feature will surely please those who regularly optimize SQL queries and who until then had some difficulties because of the lack of tools.
Do you have a question or a suggestion about this post? Contact me!
