9
Current State
In your "good" case the plan looks as below. The upper branch is sorted by SalesOrderID (and therefore also by SalesOrderID,ProductId as the grouping ensures there is only one row per SalesOrderID). The lower branch reads the index in SalesOrderID,ProductId order and they are merge joined together.
Your "bad" case uses a typical execution ...
4
Trying to get the better performing execution plan structure by adding hints
ImplRestrRemap
The query rule ImplRestrRemap is used on the secondary query when the index is added as a result of it being estimated to be cheaper in query bucks than the merge join. This is more of a consequence of the problem. Why it has a lower subtree cost than using a merge ...
Only top voted, non community-wiki answers of a minimum length are eligible
