THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

A new (to me, and possibly you) SSMS feature - Query Plan Comparing

Wow, Microsoft has really changed in culture recently. This new rapid release cycle for SSMS is seemingly paying dividends in a major way. In a recent build of SSMS (okay, perhaps not "recent", more like this October of 2015, according to this blog by the MSSQL Tiger Team:), they added the ability to compare query plans. I tried it on a very large query with a lot of differences, and it was kind of hard to follow, but that is true with any large plans. Even using the greatest query plan reading tool of them all, SQL Sentry Plan Explorer I was lost in the details.  But for your typical, make a change to a query/index and see what has changed, it is pretty nice.

As a quick example, take the following query using WideWorldImporters:

SELECT *
FROM   Sales.Invoices
        JOIN Sales.InvoiceLines
            ON Invoices.InvoiceId = InvoiceLines.InvoiceId
WHERE  AccountsPersonID = 3105;

Let's drop the index on the AccountsPersonID first (DROP INDEX FK_Sales_Invoices_AccountsPersonID ON Sales.Invoices; ), and execute the query, after enabling the Actual Query Plan. 360 rows will be returned, and you will see the plan as the following (along with a missing index hint):

image

Right-click the query plan and save the execution plan somewhere. Next, add back the index (using the script from SSMS with some formatting help from SQL Prompt:

CREATE NONCLUSTERED INDEX FK_Sales_Invoices_AccountsPersonID ON Sales.Invoices
(
    AccountsPersonID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON USERDATA;
GO

EXEC sys.sp_addextendedproperty @name = N'Description',
    @value = N'Auto-created to support a foreign key',
    @level0type = N'SCHEMA', @level0name = N'Sales',
    @level1type = N'TABLE', @level1name = N'Invoices',
    @level2type = N'INDEX',
    @level2name = N'FK_Sales_Invoices_AccountsPersonID';
GO

Then execute the query again and you will see the following plan:

image

Right click the plan and choose: Compare Plan. This will give you an open file dialog to choose the file we stored earlier. This will give you a few windows. Graphically it gives you (by default, the following view that highlights the operators that are similar) In this case, the segments for join to the InvoiceLineItems are the same except for the cost. In the Compare Options window you can also have it highlight the dissimilar operations as well. Also in the compare options it tells you what each of the things it has highlighted is, in this case it only highlighted Key Lookup operators in the red, and Index Seeks in blue:

image

Beyond this, in the properties you can see specific differences in the plan by selecting each operator. For example, with the select operator selected as I have in the image above, you will see properties such as the following:

image

You can see the Actual Number of rows highlighted here, along with an explanation of what this means. This along with other bits of information that is used, estimated number of rows, estimated costs, amount of time to compile, estimated available memory, estimated degree of parallelism, set options, wait stats, etc. Clicking on each operator you can see comparisons between them as well. The ones that have similar operations will automatically jump to the other when you click on it, and the others you can chose any two to compare.

All in all a dandy amount of information that one can find about two query plans, easily being able to compare two operators. When the query plan gets a lot larger, it can still be hard to pinpoint big differences (I had a query with 50+ operators, and the plan was wildly different from SQL Server version to version, so in that case it mainly told me how wildly different they were!) but one more tool to help look at query plan differences is definitely a great thing!

Published Thursday, January 26, 2017 9:59 PM by drsql
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Joshua Hoehne said:

Thanks for sharing. New to me. Looking forward to trying it out. I have enjoyed the more frequent releases of SSMS as well.

January 27, 2017 11:41 PM
 

Pedro Lopes (MS) said:

It's been there since late 2015. Some more fearures coming there. Thanks for sharing! https://blogs.msdn.microsoft.com/sql_server_team/tag/comparison-tool/

February 17, 2017 4:34 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement