Comparing two query plans

3

June 14, 2016 by Kenneth Fisher

T-SQL Tuesday This month my friend Michael J Swart(b/t) would like us to talk about the new 2016 version of SQL Server. The feature I want to talk about has been around for a bit. Not because it’s not part of SQL 2016 but because it’s part of SQL Server Management Studio. SSMS is no longer tied directly to the rest of SQL Server and the 2016 version has been available for a little while now.

One of the new/not so new features that I’ve been playing with off and on is the ability to compare two query plans. I’m still relatively new at performance tuning and I frequently like to compare the query plans from before and after any changes I make. Historically I’d bring up both plans next to each other and walk through them section by section. You can see how this method would be a bit of a pain, particularly for large plans or plans that have changed quite a bit. But SSMS 2016 can now help us out.

Lets say for example I’m working on the view [AdventureWorks2014].[HumanResources].[vEmployee]. I decide that the OPTION FAST will speed the overall performance up.

First I run the view without any changes, displaying the execution plan.

PlanCompare1

Then I right click in the plan and select Save Execution Plan As… and save this plan.

PlanCompare1A

Next I run the view with my changes, also displaying the execution plan.

PlanCompare2

You can see these plans are of radically different shapes. Even given it’s a relatively simple plan it might take a bit to go through the differences manually. So again I right click on the execution plan, but this time I select Compare Showplan.

PlanCompare3

This brings up a file search window. I use it to select the plan from the first query. And voilà!

PlanCompare4

You can see we have a fair amount of information here. In the two plans you can see that some elements are highlighted with various colors. Underneath them we have a key for those colors. And off to the right is a comparison of the properties of the currently selected element.

The important thing is those colors. Given how much the plan shape has changed those colors help us track sections of the plan that do the same thing. In this case, for example, the red dotted line is where we do an index seek on the EmailAddress table. But we can only really see it in the lower plan. But by clicking on that area it becomes the central area of both plans.

PlanCompare5

Since this is a single element we can also look off to the right and see all of the properties that are not the same.

Remember that this is a brand new (sort of) feature. It’s progressed quite a bit since it first came out and I expect it to continue to improve. Even as it stands however, it’s a very useful tool.

3 thoughts on “Comparing two query plans

  1. […] Kenneth Fisher (@sqlstudent144) Comparing two query plans My friend Ken introduces the SSMS feature of comparing query plans. What I Thought: I have to admit […]

  2. […] Kenneth Fisher Blog post – Comparing two Query Plans […]

  3. […] information you aren’t looking for. A few weeks back Stephen Bennett (b) linked to my post Comparing Two Query Plans. Since I always find that interesting I followed the link and wound up at his post 3 Reasons to❤ […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,607 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: