Query Store Force plan feature doesn't appear to be enforcing the plan.
I'm aware of Query Store – Forced Doesn’t Always Mean Forced; however, my plan may not change insignificantly, but query optimizer may continue to choose incorrect indexes, loop choices etc.
Basically: it doesn't honor my forced plan choice. I've forced many plans and It Just Doesn't Work.
- There are 0 failure counts or reasons when I look at
sys.query_store_plan
force_failure_count
. - Extended event
query_store_plan_forcing_failed
doesn't yield anything. 0 Events.
For example, a plan which was forced on 20.09. Only 1 compilation happened to use forced plan.
The plans differ wildly, one using Hash Match join with INDEX 1, the other using Loop Join with INDEX 2.
Version: Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) – 13.0.4202.2 (X64)
What am I missing here?
Best Answer
From sys.query_store_plan (Transact-SQL) (emphasis added)
You are using a cursor.
You can often force a cursor plan with a plan guide instead. See Using the USE PLAN Query Hint on Queries with Cursors.
And yes, I agree that the current situation does not make for the best user experience. I would expect that attempting to force a plan for something that cannot (currently) be forced would raise an error or warning, or log something. You could log a Feedback item to request an improvement in this area.
As of SQL 2019 CTP 2.3 forcing query execution plan for Fast Forward and Static cursors are supported