OPTIMIZE FOR UNKNOWN uses average distribution for all parameters (same effect as trace flag 4136).OPTIMIZE FOR UNKNOWN) uses average distribution statistics for a particular parameter.The OPTIMIZE FOR = value) query hint builds a reusable plan based on a specific value.SQL Server provides a range of query hints and other options to tune the behaviour of parameter sniffing: Parameter sniffing is a heuristic optimization: It works better than using average values on most systems, most of the time.
#SQL SERVER OPTION RECOMPILE ARCHIVE#
Certain applications do benefit from turning parameter sniffing off (see this archive post by the Dynamics AX Performance Team for an example).įor most workloads, disabling parameter sniffing entirely is the wrong solution, and may even be a disaster.
#SQL SERVER OPTION RECOMPILE FREE#
Memory reservations cannot usually grow during query execution, regardless of how much free memory the server may have. Because memory is reserved before query execution starts, a parameterized plan based on average distribution values can spill to tempdb for common parameter values that produce more data than the optimizer expected. This also sounds like a reasonable approach (and can help avoid the situation where the plan is optimized for an unusually selective parameter value), but it is not a perfect strategy either: A plan optimized for an ‘average’ value might well end up being seriously sub-optimal for the commonly-seen parameter values.Ĭonsider an execution plan that contains memory-consuming operators like sorts and hashes. When parameter sniffing is disabled, SQL Server uses average distribution statistics to choose an execution plan. 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.
0 kommentar(er)
