SQL Server Query Store: Usability Soft Spots -- Part 1https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft.html
SQL Server Query Store: Usability Soft Spots -- Part 2https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft_16.html
Summary of this post: comb through the surprise plans, finding an example that looks like the description of "morally equivalent plans"... and one that kinda doesn't.
A reminder of where this set of tests is currently.
In Part 2, the repro code was tweaked a bit, in order to no longer use a tempdb table in a CTAS statement ("SELECT... INTO", Create Table As SELECT) and to TRUNCATE results table between iterations rather than DROP. After 1600 iterations of the test query with some filler queries also executed with each iteration, 19 plan_id values were displayed in Query Store "Tracked Queries".
Attempting to force each one of those plan_id values from Query Store resulted in 2 successful forces and 17 uses of surprise plans!
A grid of all 34 plan_id values is below. Two interesting things are highlighted. In the blue box a single row is highlighted, where plan_id 195 produced plan_id 292 when forced. That's unusual because it's the only example in the list where one of the original 17 plans produced another one of the 17 originals as the "Surprise Plan". The other box highlights two consecutive rows. Plan_id values 814 and 899 both produced "Surprise Plan" plan_id 3250.
OK, let's see if any of those "surprise plans" resulted in a last_force_failure_reason_desc.
Nope, not a single one is listed as a "force failure", even though the plan forced at the time was not used.
Let's compare plan_id 195 and 292.
That's pretty much what we'd expect from a "morally equivalent plan". Both plans have the same shape. All joins and plan operators are in the same location of the two graphical plans.