on March 12, 2018
I recently wrote a fairly complicated post, called “Forced Plan Confusion: Is_Forced vs Use Plan = True.”
You do NOT need to go read that post to understand this one. I think I’ve found simpler way to explain the most important part of that post - and why it’s A Very Good Thing.
Plan forcing in Query Store is clever
The gist of this post is that plan forcing in Query Store is not the same as just putting duct-tape on an execution plan.
Query Store is a little more flexible than that. It has an understanding that if you force something too tightly, it may break.
It leaves a little wiggle room. SQL Server may compile a “Morally Equivalent Plan” if a query plan has been forced, but the optimizer looks at the plan and the “compile for” values and sees that the query plan is the wrong size pants for that query.
Step 1: I put a plan in Query Store
To get this party started, I restore the WideWorldImporters sample database and enable Query Store. I run a query and get its plan into cache.
The plan looks like this – notice that @packagetypeid is parameterized…
This plan has an estimated cost of .0067516. It thinks one row is going to flow through the whole query.
Step 2: I force that plan
I run a query against the Query Store DMVs to figure out the query_id and plan_id for this query.
Then I force it by running:
exec sp_query_store_force_plan @query_id=1, @plan_id=1;
GO
That one-row plan you see above is now forced!
Step 3: I cause a recompile to happen
The forced plan is currently in my execution plan cache. I want to cause a recompile to happen, so I clear out the cache for this database on my test instance:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
The next time my query runs, it’s going to get a fresh compile. Yes, it has a forced plan, but let’s see what happens.
Step 4: I run the same query, but with a different value for @packagetypeid
When SQL Server compiles my query, it doesn’t blindly give me the exact forced plan with its one row estimates.
Instead, it sniffs the value of my parameters. It uses that sniffed value to look at the statistics. And it sees that I’m pretty likely to have more than one row flowing through this plan. Based on the statistics, it looks more like 221,241 rows!
So, clever SQL Server gives me a “morally equivalent plan”.
Meet the “morally equivalent plan”
Notice that this plan has the same shape as the plan above. That’s the “moral equivalency.” We’ve got our Seek -> Nested Loops -> Key Lookups. Compute Scalar feeds into Stream Aggregate, etc.
But there are some differences!
- We have thicker row estimate bars, based on that statistic that was sniffed
- My plan estimated subtree cost is 51.0062 (the forced plan is .0067516)
- My plan has a green hint that points out my indexing on OrderLines isn’t so great – and my costs are different on my operators!
- The Key Lookup is now estimated at 97% of the work, whereas before it was 50%
- SQL Server isn’t thrilled about doing that lookup 221,241 times. It was no big deal to just do it once, so there was no missing index request when this was optimized for a different parameter.
The “morally equivalent plan” has its own query_plan_id, query_plan_hash, and shows up as separate in Query Store
Both the forced plan and the “morally equivalent” plan are visible in Query Store. It can be a little confusing to identify them if you haven’t seen them before.
- The forced plan has is_forced = 1 in sys.query_store_plan and will have a check mark in the Query Store reports.
- The “morally equivalent plan” will have “Use Plan=true” in the properties of the left-most operator in the plan.
The good news: if you open the “Queries with forced plans” report, both of them show up there.
Same shape, sized to fit the parameters it compiled with
I think this “wiggle room” is a terrific feature for forced plans.
Forcing plans is a somewhat crude thing to do – just because a forced plan works better today, doesn’t mean that the forced plan is going to be great tomorrow, if a bunch of my data changes. Or if a different customer is running the procedure, one who has quite a different data profile.
This feature is kind of like a stretchy waistband for the sweatpants of an execution plan. Maybe you need a bigger memory grant than the one forced with the original plan? Here’s a little wiggle room.
Plus, if I happen to check up on my forced queries in Query Store, I can notice that often when this runs it’s saying it could use a little indexing help. And just the fact that I’m sometimes getting a “morally equivalent plan” is an indication to me that I haven’t forced something that’s perfect, I should look deeper.
I love that!
I didn’t come up with the term “morally equivalent plan”
I heard this term from Erland Sommarskog, who said that he heard the term for Conor Cunningham. I love that it’s unique, not an acronym (yet), and it seems like a good description to me - so let’s popularize it among those of us who talk about plans :)
And if you’d like to play with the code for this, it is based on a sample from Jovan Popovic published under the MIT license: grab it here.