SQL Server with Mr. Denny

Apr 5 2017   4:00PM GMT

Compare Plans in Query Store

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: