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.
You’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.
One Response
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.