Parameter sniffing is a common challenge for query tuning. The usual solution we recommend is to apply a recompile option, rather to a stored procedure or a query. If the development team gets used to this, suddenly you will see recompile options all around.

Query Tuning 101

The parameter sniffing problem appears when the data is uneven distributed on one column used as predicate. As a result, the perfect plan for the query will be different according to the value used on the predicate. Applying a recompile option solves the parameter sniffing problem because the query will be recompiled for each set of parameters, ensuring the best query plan possible.

Here are some examples about how the plan can be affected:

• A key lookup can be applied if the number of rows returned is small or an index scan will be applied if the number of rows returned is too big.
• A join can be made using nested join or hash join, according to the number of rows returned.
• The memory allocated can become too much, or to low, and if it’s too low, it will cause spills to tempdb and performance problems, and this will be terrible for performance.

In order to ensure the plan will be recompiled on every execution, we have some different options:

• We can apply the clause ‘With Recompile’ to a stored procedure. It will make the procedure be recompiled on every execution.
• We can apply the ‘With Recompile’ clause to the ‘Execute’ statement when executing the stored procedure. This will cause the procedure to be recompiled before the execution.
• We can apply the clause ‘Option (Recompile)’ to a query, even if the query is inside a stored procedure.

However, the solution has a price: We are losing the power of the query plan cache, forcing a recompilation on every execution, what affects performance. We are only choosing the cheaper option.

New Solutions for Parameter Sniffing

Microsoft is aware of this and SQL Server created many features to help us solve the parameter sniffing problem. These are three of them:

• Query Store can identify queries suffering with parameter sniffing
• Memory Grant Feedback can solve some memory allocation problems caused by parameter sniffing
• Adaptive joins can solve problems with join selection caused by parameter sniffing
• Batch mode over row store allows adaptive joins to be applied over row mode queries

That’s great! We can continue solving our problem with the recompile options but now these great new features will jump in and make our queries even better, right?

Wrong!

Memory Grant Feedback and Adaptive Joins are part of the Adaptive Query Processing while Batch mode over row store is part of its big brother, Intelligent Query Processing.

So, what?

Adaptive Query Processing is based on the idea of changing some behaviours of the query plan during the execution, without a new query compilation. This is in some ways the opposite of our traditional solution, the recompile options. We can choose always to recompile the plan or we can use the new Adaptive Query Processing features, but we can’t use both, they will be, at most, useless.

We need to analyse each one of them to understand.

Adaptive Joins

Adaptive Join is a query plan operator capable to create alternate paths inside the execution plan. These alternate paths allow SQL Server to choose the best join option according to the number of rows returned on each execution. In other words, a different behaviour on each execution according to the number of rows returned.

This solution handles the parameter sniffing problem for join selection in a great way: The decision of what type of join will be used is made during each execution, according to the number of rows returned. Parameter sniffing will not affect join type decision anymore.

However, if we apply any recompile option, the adaptive join loses its meaning. Each compilation can decide what type of join is best for the plan, there is no need of the adaptive join at all.

Adaptive Joins and Recompiles

This creates some resulting scenarios not so good for us:

• The widespread use of recompile options will prevent you from enjoying the benefits of this new feature. This means your queries could be better, they could avoid paying the recompile price, but having the recompile mindset spread among developers will prevent deeper analysis.
• Besides the recompilation price, you will still have the adaptive join in your query plan. Probably the cost is meaningless, but it still creates some extra steps during compilation and execution.
• There is, indeed, one chance that even using recompilations, the adaptive join will still improve the query. How? The compilations are based on existing statistics, while adaptive join is based on the actual rows during the execution. If the statistics were not updated, the plan without adaptive join could be wrong, while the adaptive join would not be affected (unless the statistics are so bad you end up without the adaptive join at all). This creates a hide-and-seek game: Your performance improved, cool! But it’s hiding the fact it could be better, because you could get rid of the recompilations, that’s bad. It’s also hiding the fact you are not making a good statistics maintenance and that is very bad.
• Who will fully enjoy the new features without many concerns are exactly the ones who were not taking much care of the query performance and haven’t used recompile options to solve parameter sniffing. The most careful ones will need to deal with mindset change in the DBA and development team leading to many strange scenarios.

Memory Grant Feedback

Parameter sniffing can make the amount of memory needed by one query vary too much. This variation will create spills to tempdb, making the query way slower.

Memory Grant Feedback can solve this problem in some situations. It’s simple: If a difference is found between the memory needed and the memory allocated, this feature changes the query plan directly in the cache for the next execution. As a result, while one execution goes bad with a wrong amount of memory allocated, the next one will be fixed.

The Effect over Sniffing

The effect over parameter sniffing is only partial. Let’s imagine the variation of the parameter values generate two different plans according to the value. If each plan is executed a considerable number of times before a different plan is needed, the Memory Grant Feedback will improve the query.

However, if different plans are needed on a very high frequency, such as on each execution, the Memory Grant Feedback will not be useful. This feature identifies the problem, change the plan and the improvement will happen on the next execution. If the next execution needs a different plan or different set of resources, the change was useless. After 32 useless changes caused by the Memory Grant Feedback, the query optimizer disables this feature for the query plan.

Recompile

The use of recompile option turns the feature completely useless. This feature is based on the same plan executed multiple times. Recompiling the plan on every execution invalidates the feature.

Conclusion

It doesn’t matter how simple new features are, the technology evolves way faster than company and department procedures. In order to fully enjoy new features, you may need some mindset change.