Tag Archives: Compare Execution Plan

Compare Execution Plans in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , ,

Comparing two execution plans is one of the new feature which is getting introduced in Sql Server 2016. This will be one of the good addition to the Sql Server features set. Many a time we come across a scenario where we need to compare the two execution plans. For example some time we want to see what is the difference in the execution plan after making some changes to it. Sometimes we come across a scenario where we observe that some stored procedure is perfectly working fine in development/System test environment but not in the production environment. In such scenario comparing the Dev/QA execution plan with production execution plan gives us the clue on what is going wrong. Without this feature currently we open the two execution plans in separate window and then we manually compare, it is tedious to figure out quickly what is going wrong.

[ALSO READ] Live Execution Plan in Sql Server 2016

Note: This article is based on the Sql Server 2016 CTP 3.0. Some of the views mentioned in this article may change in the RTM. Will try my best to keep this article updated.

Let us understand this Execution Plan comparison feature in Sql Server 2016 with examples. Let us create a Customer table with 500,000 records by the following script

CREATE DATABASE SqlHintsJSONDemo
GO
USE SqlHintsJSONDemo
GO
CREATE TABLE dbo.Customer( 
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50), 	PhoneNumber NVARCHAR(20),
	CreationDate DATETIME, ChangeDate DATETIME)
GO
INSERT INTO dbo.Customer(Name, PhoneNumber, 
                         CreationDate, ChangeDate)
SELECT TOP 500000 NEWID(),
 100000000 - ROW_NUMBER() OVER (ORDER BY SC1.object_id),
 GETDATE(), GETDATE()
FROM SYS.all_columns SC1
        CROSS JOIN SYS.all_columns SC2

Enable the actual execution plan in the SSMS window and execute the below query and save the execution plan as FirstExecutionPlan.sqlplan

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE PhoneNumber = '99750000'

RESULT:
First Execution Plan without Index on PhoneNumber

Now let us add a index on the PhoneNumber column by the following script:

CREATE INDEX IX_Customer_PhoneNumber 
  ON dbo.Customer(PhoneNumber)

Now re-execute the previously executed query to get the Customer record based on the PhoneNumber by selecting the actual the execution plan option in the SSMS window. And save the resultant execution plan as SecondExecutionPlan.sqlplan

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE PhoneNumber = '99750000'

RESULT:
Second Execution Plan with Index on PhoneNumber

Now open the first execution plan (i.e. FirstExecutionPlan.sqlplan) from the Sql Server Management Studio (i.e. SSMS). Then right click anywhere in the opened execution plan window and we can see a new menu option “Compare Showplan” as shown in the below image:

Load the first execution plan to compare

Select the Compare Showplan option and load the SecondExecutionPlan.sqlplan which we have saved previously to compare with the already loaded FirstExecutionPlan.sqlplan. Then the resultant ShowPlan Comparision window will be as shown below:

Execution Plan Compare View

In the ShowPlan Comparision window select Clustered Index Scan node from the First Execution Plan and Index Seek node from the Second Execution Plan and then right click on one of these nodes and go to Properties context menu option. This will open up a side by side comparison properties window as shown in the below image where we can compare the various properties of these two nodes.

Execution Plan Comparision with Property window

In the ShowPlan Comparision window if we want to see the execution plans in side-by-side view instead of top and bottom view, then right click anywhere in the window and click on the Toggle Splitter Orientation option to make it Side-by-Side or Top-And-Bottom view.

Let us execute the below Statement by selecting the actual execution plan option in the SSMS and save the execution plan as ThirdExecutionPlan.sqlplan. The difference between this select statement and the previous select is: here the SELECT statement has the TOP clause in it:

SELECT TOP 1 *
FROM dbo.Customer WITH(NOLOCK)
WHERE PhoneNumber = '99750000'

RESULT:
Third Execution Plan with Index on PhoneNumber

Close all the open windows and then open the SecondExecutionPlan.sqlplan from SSMS. Right click anywhere in the execution plan window and from the context menu select the option “Compare Showplan” and load the ThirdExecutionPlan.sqlplan. It will bring up the following ShowPlan Comparison window as shown in the below image.


Second and Third Execution Plan comparision

From the comparison window it is clear that matching nodes are highlighted in the same color, it means no change between the two nodes of the two different plans. And TOP node is not colored, because it is not matching with any other node.

If you select one of the matching node in one plan it will automatically marks the corresponding matching node in other plan also selected. For example here I have selected the Index Seek node in the first/top plan (i.e. selection is highlighted by blue border), it automatically selects the Index Seek node in the second/bottom plan as shown in the below image.


Second and Third Execution Plan comparision Node Selection
[ALSO READ]: