Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Compare Showplan Option


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

Just released in SQL Server 2016 CTP 2.4 is a new feature that will allow us to compare execution plans. The main purpose of this feature is to provide a side-by-side comparison of two execution plans. This makes it easier to find similarities and changes on each plan. This is a very nice enhancement that aids in troubleshooting issues such as understanding the impact of rewriting queries or observing how a design change, such as an index, may impact the plan. In the tip below, we’ll go over how it works.

Solution

For this tip, I’m using the AdventureWorks2014 database and I’m going to use the following query:

SELECT FirstName, LastName, CreditCardID 
FROM Person.Person p 
INNER JOIN Sales.PersonCreditCard c ON p.BusinessEntityID = c.BusinessEntityID
WHERE p.ModifiedDate > '2014-01-01'
        

Open SQL Server Management Studio (SSMS) 2016 and execute the query with SET STATISTICS IO ON and “Include Actual Execution plan“ enabled.

SQL_Server_Management_Studio

Click the Messages tab and you’ll notice that we’re doing 3819 logical reads on the Person table. This is a sign that this query can be tuned. We’d like to get this number to the lowest possible.

SET_STATISTICS

Click the execution plan tab and you’ll see the plan along with some Missing Index Details.

Missing_Index_Details

The execution plan is what we’ll focus on in this tip, but I wanted to look at STATISTICS IO also to see if we can improve the number of logical reads.

To be able to compare plan we’ll need to save the execution plan. Right click anywhere in the plan window and select Save Execution Plan As… Choose a name and location and hit Save.

Save_Execution_Plan

Back to the execution plan, right click anywhere in the window and choose Missing Index Details. The query processor estimates this index will improve performance by 92%:

 USE [AdventureWorks2014]
GO
CREATE NONCLUSTERED INDEX [IX_Person_ModifiedDate_INCLUDES]
ON [Person].[Person] ([ModifiedDate])
INCLUDE ([BusinessEntityID],[FirstName],[LastName])
GO

After reviewing this index, I think it’s a good idea to go ahead and create it by running the script.

Now that the index has been created, I want to re-run my query (with SET STATISTICS IO ON and the execution plan enabled).

Looking at the Messages tab you’ll see our logical reads when down from 3819 to 38. So far so good.

execution_plan

On the execution plan tab, you’ll see the plan changed and the Missing Index Details recommendation is gone.

Missing_Index_Detail

Right click on the new plan and click Save Execution Plan As… and save the plan to the same directory as the original query.

NONCLUSTERED_INDEX

Now, to compare plans, open the first plan in SSMS. You can either use File -> Open -> File or simply drag and drop the file in the SSMS window. Once the plan is open, click anywhere in the window and you’ll see an option to “Compare Showplan”

Compare_Showplan

Click Compare Showplan and you’ll be able to open another saved execution plan for comparison. Once this is opened you’ll notice two execution plans in the same window. If the properties window isn’t open, right click in the plan window and select Properties.

properties_window

You’ll notice that the there is an operator in both plans that’s highlighted. This is to point out the operator that is essentially 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 will be able to compare the properties at that level also.

The Properties window shows a lot of information, but from this example we can see some improvements were made just by adding the index. The Compile CPU, Compile Time, and Estimated Subtree Cost all went down. On the plan before the index you can also see the Missing Index Details and the impact %.

query_tuning

I’m excited for the future of this tool and hope Microsoft can put more emphasis on it as I believe it will help query tuning tremendously.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools