Starting with SQL Server 2016, parameter sniffing can also be disabled at the database level, using the PARAMETER_SNIFFING argument to ALTER DATABASE SCOPED CONFIGURATION. Both apply from SQL Server 2005 Service Pack 4 onward (and slightly earlier if you apply cumulative updates to Service Pack 3). The trace flag is also supported for per-query use via the QUERYTRACEON query hint. Parameter sniffing can be disabled using documented trace flag 4136. Serious performance issues can arise when this plan is reused for different parameter values that generate a much larger number of rows. Such plans will often use single-threaded execution, nested loops joins, and lookups.

One such scenario occurs when the atypical value is highly selective, resulting in a plan optimized for a small number of rows. It is not easy to predict when a particular execution plan will be recompiled (for example, because statistics have changed sufficiently) resulting in a situation where a good-quality reusable plan can be suddenly replaced by a quite different plan optimized for atypical parameter values. This can result in a new cached plan (based on the sniffed atypical parameter value) that is not good for the majority of executions for which it will be reused. Whatever the exact cause of the plan recompilation, there is a chance that an atypical value is being passed as a parameter at the time the new plan is generated. A recompilation may be triggered for all sorts of reasons, for example because an index used by the cached plan has been dropped (a correctness recompilation) or because statistical information has changed (an optimality recompile). This sounds reasonable enough (even obvious) and indeed it often works well.ДЄ problem can occur when an automatic recompilation of the cached plan occurs. The implicit assumption is that parameterized statements are most commonly executed with the most common parameter values. When parameter sniffing is enabled (the default), SQL Server chooses an execution plan based on the particular parameter values that exist at compilation time. An execution plan that is efficient for one parameter value may not be a good choice for other possible parameter values. These are all good things, provided the query being parameterized really ought to use the same cached execution plan for different parameter values. Query parameterization promotes the reuse of cached execution plans, thereby avoiding unnecessary compilations, and reducing the number of ad-hoc queries in the plan cache.

Recompile stored procedure