Sort operator positioning in an execution plan

Question

I am troubleshooting a performance issue and trying to get a stable execution plan for a reporting query. Links to the actual plans are below:

Good vs Bad

If you look at the Good plan, the sort is after the table joins. For the bad plan, the sort is just before the last join. In the case of bad plan since the sort is before the last join, and since the sort's estimated number of rows is 5000, SQL Server has decided to go with a nested loop join, which is slowing the query.

I do not want to use table join hint or change the variable on the TOP.

What are the strategies I can use in this scenario to get a consistent plan?

This query runs approximately every 5 minutes.

Answer 1

It looks like you have a classic parameter sniffing issue. As far as I can tell your queries are identical but both of them use different cached plans. The cached plan that you get depends on the input parameters for the first run. I assume that there's a plan that works well enough for all inputs and you want that plan. However, depending on parameter values you might get unlucky and end up with a plan that works better for some inputs but poorly for others.

If possible I would add a RECOMPILE hint to the query. This will prevent the query optimizer from using a cached plan for the query but it will see the values of the variables when making the plan. As a general rule of thumb you'll get better performance if you can give more information to the optimizer. The downside of this approach is that the query plan will be recompiled each time, but this is only a three table query. I wouldn't worry about it unless the query runs hundreds of times per second.

Another option is to use the OPTIMIZE FOR query hint. This will give you a cached plan that isn't based on input values, so the key is getting a query plan that works well enough for all possible input values. In your other question you implied that you weren't satisfied with the default guess of 100 rows for TOP so you can force the query optimizer to optimize for a specific TOP value like follows:

OPTION (OPTIMIZE FOR UNKNOWN, OPTIMIZE FOR (@top = 5000));

None of the input variables will be sniffed to form the plan. That syntax may look odd but it's allowed:

If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.

If neither one of those options works and you're confident that you have a query plan which works well you could look into using plan guides.