Deepthi Goguri explains why that forced query plan isn’t behaving the way you might expect:
One of the advantages of using Query store is the ability to force a plan. Once you force a plan, there may be reasons when the optimizer still chose to create a new execution plan without using the forced plan. During this time, a failure reason is stored in the Query Store. When the forced plan cannot be used, the optimizer goes through the regular compilation and optimization phases to create a new plan but it doesn’t actually fail the query itself. It executes in a normal way with the new plan being created.
But what causes these failures and how to know the reason behind these forced plan failures?
Read on to find out.