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:
List item
- 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
Best Answer
Adding join hints should be a last resort. There should be ways to rewrite the query / add indexes to get a more consistent result.
These plans are also estimated execution plans, in this case only you knows how well / bad the actual query will perform.
If the issue is parameter sniffing,
OPTION(RECOMPILE)
would be the easiest solution.Is the
LEFT JOIN
only used for filtering? ANOT EXISTS
might work better to filter earlier.Having said all that, with the limited information given, here are some possible, quick rewrites.
Rewrite #1
LEFT JOIN
toNOT EXISTS
The
OPTION(RECOMPILE)
is added to get better estimates based on the parameters provided.Rewrite #2 Also removing the
OR
by usingUNION
Rewrite #3 Adding an additional temp table to store the inner join
By adding a temp table to split up the queries, the optimizer might get better estimates on the final query.
Closing note
If there is more information, like the table definitions and some sample data, more can be done, these three rewrites simply look like the fastest and easiest wins at the time of writing.