I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between good and bad execution plans). This way I get SQL Server to choose most of the plan, while enforcing the few joins I know are necessary to be able to finish the queries.
In the execution plans below, I want to enforce the join types to be somewhat the same and will therefore use local joins hints also for these. However, I was wondering if I am able to trigger other actions in the execution plans as well, such as:
- SORT(Distinct Sort)
- Stram Aggregate(Aggregate)
Are these actions something I can choose, or are they dependent on join types/order choosed during the query?
Both plans are created by XML extracted from Query Store.
Good execution plan: https://www.brentozar.com/pastetheplan/?id=HyYMn7K2V
Bad execution plan: https://www.brentozar.com/pastetheplan/?id=Hka6i7Yh4