What is Parameter Sniffing and how do you handle it?

Parameter sniffing occurs when SQL Server compiles a plan using the initial parameter values it sees, then reuses that plan for subsequent executions. If data distribution is skewed, one plan may not fit all parameter scenarios.

Symptoms: Some calls are lightning fast, others very slow, using the same procedure and query shape.

Handling strategies:

• Use OPTION (RECOMPILE) for highly skewed queries where compilation cost is acceptable.
• Use OPTIMIZE FOR UNKNOWN or OPTIMIZE FOR (@param = ...) hints to choose more robust plans.
• Capture parameters in local variables inside the procedure to discourage sniffing and produce more average plans.
• Split logic into separate procedures for different parameter ranges if patterns are distinct.
• Use Query Store to force stable plans when appropriate.