Compare Plans in Query Store

Published On: 2017-04-05By:

Have you ever been looking at an execution plan in the SQL Server Query Store reports and wanted to be able to see what the differences between that plan and another plan were?

Did you know that you can?

It’s a couple of step process, but you can compare the plan in the Query Store to any plan that is saved as a “.sqlplan” file.  Simply right click on whatever plan you want to compare to (either a real plan, estimated plan, or plan from the query store) and click “Save Execution Plan As” and save the plan to your local hard drive.  Then locate the plan you want to compare in the Query Store.  Right click on that plan, and select “Compare Showplan”.

That’ll give you a pretty GUI like the one shown below (click to enlarge).  By default anything shaded in red in both plans will be identical. Anything not shaded you’ll want to look at as those are different in some way.  Looking at my queries below you’ll notice that they look the same. What’s different on these is actually the costs, row counts, etc.

compareYou’ll also notice the two sets of properties with the little yellow not equal too signs next to them. That tells you which values from the properties don’t match.  That’ll give you a quick easy visual identifier as too what is different between the two execution plans.

Personally I’d like to just be able to right click on a couple of the plans in the Query Store and compare them without saving, but I didn’t see a way (doesn’t mean there isn’t one, or that there won’t be one in the future).

Denny

The post Compare Plans in Query Store appeared first on SQL Server with Mr. Denny.

Contact the Author | Contact DCAC

One response to “Compare Plans in Query Store”

  1. Pedro says:

    With a couple plans for dame qry in query store, you can directly select both (use shift key and click) and click the comparison plan button.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via