SQL SERVER – Parameter Sniffing and Bad Plan

Disabling parameter sniffing does not guarantee that a bad plan becomes good. That remains my answer to the customer question.

Compilation can use current parameter values. A reusable plan can suit one value and poorly serve another distribution. Without sniffing, estimates use model-dependent assumptions such as statistics density. They are not always the exact average of all values.

SELECT name,value,value_for_secondary FROM sys.database_scoped_configurations WHERE name IN (N'PARAMETER_SNIFFING',N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION');

Reuse saves compilation CPU and is not inherently the cause of every poor plan. Inspect skew, statistics, estimates, indexes and query shape. Identify the parameters associated with the problem.

Targeted recompilation uses current values but costs compilation work. Query changes, indexes, parameterization and plan management offer other options. Supported engines and compatibility levels also offer parameter-sensitive plan optimization.

I previously said recompilation had no replacement. That was too absolute. Compare representative parameter sets and concurrency. Choose targeted evidence before changing a database-wide setting.

Reference: Parameter-sensitive plan optimization.

Related reading

• Comprehensive Database Performance Health Check
• SQL in Sixty Seconds
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

A sniffing setting is not a complete tuning strategy, it is a compilation choice to evaluate with representative parameters.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.