Strange behaviour of force plan in query store

Question

I force plan in query store. Plan is connected to procedure in a job which run once a day. One of step of this job is just:

EXEC [schema].[LoadData]

Procedure [schema].[LoadData] looks like

TRUNCATE TABLE [schema].[Data];
     
    INSERT INTO [schema].[Data]
           ([A1],
           [A2],
          .
          .
          .,
          [A49]
    )                
    SELECT *
        ,CURRENT_TIMESTAMP AS [Insert TimeStamp] 
    FROM [schema].[View]   

where view is a view which contains some CTEs and use synonyms (connect to tables from different databases).

In query store execution looks like :

To test If forcing plan is working I follow below steps:

1. Run query in SSMS -> EXEC [schema].[LoadData]
2. Above execution was treated as different query so did not see anything new in Query Store for query = 7
3. DBA create a new job just with step which is running query -> EXEC [schema].[LoadData]
4. Run of above newly created job cause Plan Id = 29800

Question why execution plan was not forced ? In column "forced plan failure count" is 0.

Answer 1

The chances are that plan id 29800 is indeed forced, but query store makes this difficult to see.

Plan forcing does not guarantee new plans will be identical to the original, as documented at sp_query_store_force_plan (emphasis added):

The resulting execution plan forced by this feature will be the same or similar to the plan being forced. Because the resulting plan may not be identical to the plan specified by sp_query_store_force_plan, the performance of the plans may vary.

If the generated plan is not identical, you will see a new plan id. That does not necessarily mean plan forcing failed. When plan forcing fails, the extended event query_store_plan_forcing_failed is fired.

You can verify plan forcing succeeded by looking at the plan xml with id 29800 for the attribute UsePlan="1" in the QueryPlan element. It also appears as a property at the root of the graphical plan. Plan id 29800 will also appear in the 'queries with forced plans' report.

You might like to read Erland Sommarskog's explanation in his article Slow in the Application, Fast in SSMS?

Kendra Little also covers this issue well in What is a "Morally Equivalent Execution Plan," and Why Is It Good?

As an aside, the reason SSMS generated a different query id is almost certainly because you do not have SET QUOTED_IDENTIFIER ON in your job step, or you have your SSMS configured to SET ARITHABORT ON (as is the pointless default).