In case you haven’t heard, moral decline is everywhere. The latest generation of query operators is a prime example of this rot. “But this is programming” you say, “what do morals have to do with anything?” No. This is SQL. And for us, we have the term morally equivalent plans which allows me to finally have an ethical excuse for sprinkling a post with degenerate puns.
“Morally equivalent” describes plans that have the same shape but with differing details. For adaptive joins, Threshold Rows is one of these details. Alas, moral relativism fails again, because this is a detail that matters.
An adaptive join tipping to a scan after 2 rows is morally equivalent to an adaptive join scanning after a 10000 row threshold. You are not allowed to use Query Store to force your will on this plan – SQL Server only sells certified cage-free adaptive joins. The plan shape will be forced, but the threshold can and will change as the plan gets recompiled.
Moreover, Query Store often (but not always) treats different threshold values as separate plans, so you might force a single plan and still end up with skittles splattered across your GUI.
All of those are identically shaped plans. All of them. Oh, and that 2-row vs 10000-row threshold was not a hypothetical example and absolutely matters for performance.
So do not force a 1000-row threshold plan and expect stable performance. According the Copenhagen Interpretation of Ethics (aka normal DBAing), it’s your fault if the plan changes and starts timing out.