Understanding Plan Choice When Query Store Doesn't Respect Forced Plan
Could anyone help me understand why the plan wasn't forced


Looking through the plan XML, plan id 6194 was compiled on version 15.0.700.539 of SQL Server, while plan id 13449 was compiled on version 15.0.900.210.

The only differences of note (other than cardinality estimates) between the two plans are these:

The compute scalar was affected by a row goal (due to the implicit TOP from OFFSET / FETCH):

EstimateRowsWithoutRowGoal="170.008"


And the NL join was optimized with undordered prefetch:

&lt;NestedLoops Optimized="true" WithUnorderedPrefetch="true"&gt;


It seems that these slight differences resulted in the problem described in Andrew Kelly's article, which you linked to.  The plan is essentially the same, with minor implementation differences.  Grant Fritchey describes this as follows:


  There is a relatively obscure situation where a "morally-equivalent" plan, a plan that is identical in all the core essentials, but not necessarily perfectly identical, can be used instead of the precise plan you define. However, this isn't common.


From page 473 of his free e-book about execution plans.


  and why the one in use was so much more expensive?


The really expensive plan in use has 4 extra columns, so I think that one is not really comparing apples to apples.  The higher costs appear to mostly be due to the parameters used (and thus higher cardinality estimates).  Incidentally, it's on another completely different build: 15.0.1300.566.  Things sure do move fast in the cloud =P

These are all "new" columns coming out of the SELECT *:

&lt;ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="delete_requested_date_utc" /&gt;
&lt;ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="retention_policy_applied_date_utc" /&gt;
&lt;ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="retention_policy_type" /&gt;
&lt;ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="id_portal_user" /&gt;


The combined test you provided were all inline with this latest version of things.