Microsoft are releasing regular CTP updates for SQL Server 2016. In the very recent release (CTP 2.4), there is a new feature in SSMS that allows the user to compare execution plans side-by-side.

Yes! And, hopefully very soon, we would not need to flick between the execution plans but be able compare them side-by-side.

Let’s see how it works. As usual, I will be using the StackOverflow database for my examples.

Let’s assume this was one of my queries:

SELECT TOP 10
P.Id AS QuestionId, P.Score AS QuestionScore,
A.Id AS AnswerId, A.Score AS AnswerScore
FROM Posts P 
INNER JOIN Posts A on P.AcceptedAnswerId = A.Id
WHERE P.LastActivityDate > '2013-10-01'
ORDER BY QuestionScore DESC

The execution plans of this query looks as follows:

Note: To be able to compare plans you need to save the execution plans.

So I save this execution plan – 1_beforeIndex.sqlplan.

SQL Server is informing me of the following missing index:

CREATE NONCLUSTERED INDEX [IX_Posts_LastActivityDate_INCLUDES] 
ON [dbo].[Posts]
(
[LastActivityDate] ASC
)
INCLUDE ( [Id],
[AcceptedAnswerId],
[Score])

After having reviewed the missing index and the query usage, I decide to create it. I then re-run my query and again save the execution plan away – 2_afterIndex.sqlplan.

To compare plans, open the first plan in SSMS. Either File -> Open, or drag and drop the file in the SSMS window. Now if you right-click anywhere in the window, you will see the new option – Compare Showplan.

Click this option and you will be able to select the saved execution file to compare it to. You should now see the two plans in the same window. If the dual-properties window is not open then right-click anywhere in the window and select Properties. The dual-properties window should open on the right.

You will notice that the there is an operator in both the plans that’s brightly coloured. This is to highlight the operator that is, effectively, doing the same thing in both the plans. Clicking on any one of them allows you to compare these two directly and compare the properties. If you click the SELECT operator in both the plans you should be able to compare the properties at that level too, which I find quite useful.

This feature seems very interesting and allows you to compare plans without flicking back and forth.

I can see a number of reasons why you would want to use this:

1) Compare plans before and after addition/removal of index

2) Compare plans with different parameters

3) Compare plans before and after stats update

4) Compare plans before and after the use of query hints

5) Compare plans on different versions of SQL Server e.g. if you are planning to upgrade your database

I’m sure some of you will find this useful and are looking forward to using this in the future.

Can you think of other reasons where this could be of great use?

Thanks very much.

The post SSMS 2016 – Compare Execution Plans appeared first on SQLYSE.