The Execution plan comparison feature (in SSMS)

Did you know that you can compare two execution plans in SQL Server Management?

It’s really cool. I use it a lot, as my first stop to compare performance. Let’s take two execution plans from my series on parameter sniffing.

Demo comparing execution plans

CREATE PROCEDURE [dbo].[QueryPostType] (@PostType INT) as
BEGIN

SELECT PostTypeId, Tags, Body 
FROM Posts 
WHERE PostTypeId = @PostType

END
GO
exec QueryPostType @PostType = 1

--I cleared the plan cache before running this second test.

exec QueryPostType @PostType = 3

We’re in the StackOverflow 2010 database. The PostType parameter will give us these two execution plans.

In this example, we can see there’s different operators on the Posts table. But in the real world, execution plans are much more complex. That’s where execution plan comparison comes in. Just save one of the execution plans then right-click and pick compare execution plans.

Our first view looks like this, which isn’t super useful.

But there’s a better way. First, uncheck the box says “Highlight similar operations” and then check “Highlight operators not matching similar segments”

Great! So if we pick the SELECT operators. Take a look at all the differences that are highlighted.

We see in the parameter list that each plan was compiled for a different compiled value. Take a look at the Actual Number of Rows, and all the other differences.

Thanks for reading! I hope this was a useful starting place when you’re comparing execution plans. Stay tuned.