By: Brady Upton | Comments | Related: > SQL Server 2016
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.
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.
Click the execution plan tab and you’ll see the plan along with some 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.
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.
On the execution plan tab, you’ll see the plan changed and the Missing Index Details recommendation is gone.
Right click on the new plan and click Save Execution Plan As… and save the plan to the same directory as the original query.
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”
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.
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 %.
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
- Learn more about SQL Server 2016 here.
- To view more tips on execution plans check these out.
- Check out all of the SQL Server 2016 tips.
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
