on November 15, 2017
When you force a plan with Query Store, you may not get what you expect. Here’s an example.
My update statement has two plans recorded by Query Store
I have an update statement for a user database table which is known as “Query 15” in Query Store. Query 15 has two plans: Plan Id 17, and Plan Id 19.
Here’s Plan Id 17 - we’ll call it the “wide plan”. It has an estimated subtree cost of 401.579. I generate this plan if I compile the procedure dbo.UserDatabaseTableTest with @RowID = 800000. This plan gets ‘FULL’ optimization.
Here’s Plan Id 19 - we’ll call it the “narrow plan”. It has an estimated subtree cost of .272. I generate this plan if I compile the procedure dbo.UserDatabaseTableTest with @RowID = 8000. This is considered a ‘TRIVIAL’ plan.
I want to force the “narrow”/ trivial plan
I decide that I want to force Plan Id 19. I click the Force Plan button, confirm, and immediately get a check mark that Query Store is going to try to force this plan:
My narrow plan is nowhere to be found anymore!
Now that I’ve forced this plan, I can’t generate Plan Id 19 anymore. No matter how I compile my procedure, I end up with Plan Id 17. Even if I clear my procedure cache. Even if I restart my SQL Server.
Not only do I get Plan Id 17 (wide plan), when I look at the actual execution plan, it shows that “Use plan = true”… in other words, that it is being forced.
This update statement’s query plan shows an estimated subtree cost of .1896… (less than 1):
How does this look in Query Store’s Top 25 resource consumers report?
Good news: the report accurately shows that I’m getting Plan Id 17 consistently, although PlanId 19 is still forced (indicated by the check mark):
I say this is good news because it clearly shows that I’m trying to force something that I’m not getting, which is something very useful for me to figure out.
This is not considered a “failure”
When I check the Query Store DMVs, force_failure_count is 0. The last_force_failure_reason_desc is NONE.
Query Store didn’t fail to apply the narrow plan. Instead, it’s just deciding not to give it to me, now that I’ve forced that plan.
Seems kinda like an adolescent, doesn’t it?
Could something more be going on here? Further tests…
I decided to do some more testing to make sure the behavior I was seeing wasn’t due to something else.
Dropping the table at the beginning of the procedure didn’t matter
My procedure recreated the table being updated at the beginning of each run, using a DROP IF EXISTS / CREATE TABLE pattern. I changed this to a CREATE TABLE (if not exists) / TRUNCATE TABLE pattern.
The behavior remained the same: once I forced the trivial “narrow” plan, I always got a wide plan – no matter what @RowID value I compiled the procedure for.
Adding an ‘OPTIMIZE FOR’ hint to the UPDATE statement didn’t matter
I was quite surprised by the result of this test, but I probably shouldn’t have been!
I added an OPTIMIZE FOR hint to the end of my update statement, like this:
As long as the plan is NOT forced in Query Store, this query reliably gets the narrow update plan – because no matter what @RowID is passed in, the low estimate of 800 is “sniffed” every time the procedure is compiled.
However, once I force that narrow plan in Query Store, guess what happens?
Yep, I get a new “wide plan” which shows full optimization…
For you observant folks out there, I recreated the procedure and I changed this statement by adding the OPTIMIZE FOR hint… so the object_id for the procedure and the query number / plan id numbers have all changed.
Query Store isn’t supposed to force exact plans
When you force a plan in Query Store, you aren’t guaranteed to get the exact plan that you’re forcing. For another example of this check out Andrew Kelly’s post here. I haven’t found an example of folks from Microsoft documenting this, oddly, but it does seem to be true based on experimentation.
This experiment does raise the question for me: can you force other TRIVIAL plans with Query Store? Or will a forced plan always have FULL optimization? I am adding that to my research list for the future… but even if I identify an answer, it will very likely be an answer “for now” which could change at some point.
What to do with this information
When it comes to plan forcing, whether it’s manually done or “automatically tuned”…
- Consider plan forcing a temporary fix. Look at changing code or indexes to find a permanently stable fast plan that doesn’t require forcing.
- Look at whether the plan being forced is the plan that you’re getting after it is forced! It may not be identical, even if it’s not failing :D