Query Store Force Plan feature doesn't work 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?
From sys.query_store_plan (Transact-SQL) (emphasis added)


  Plan forcing limitations
  
  Query Store has a mechanism to enforce Query Optimizer to use certain execution plan. However, there are some limitations that can prevent a plan to be enforced.
  
  First, if the plan contains following constructions:
  
  
  Insert bulk statement.
  Reference to an external table
  Distributed query or full-text operations
  Use of Global queries
  Cursors
  Invalid star join specification
  


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


  Query Store now supports the ability to force query execution plans for fast forward and static T-SQL and API cursors. Forcing is now supported via sp_query_store_force_plan or through SQL Server Management Studio Query Store reports.