parameter sniffing

Question

Suppose we have a poorly performing stored procedure with 6 parameters. If one of the six parameters is transferred to a local variable within the stored procedure, is that enough to disable parameter sniffing or is it necessary to transfer all 6 parameters that're passed to the stored procedure into local variables within the stored procedure?

Answer 1

Per Paul White's comment, assigning a variable to a local variable is a workaround from older versions of SQL Server. It won't help with sp_executesql, and Microsoft could write a smarter parser that would invalidate this workaround. The workaround works by confusing the parser about a parameter's value, so in order for it to work for each parameter, you'd have to store each parameter in a local variable.

More recent versions of SQL Server have better solutions. For an expensive query that is not run often, I'd use option (recompile). For example:

SELECT *
FROM YourTable
WHERE col1 = @par1 AND col2 = @par2 AND ...
OPTION (RECOMPILE)

This will cause the query planner to recreate ("recompile") a plan every time the stored procedure is called. Given the low cost of planning (typically below 25ms) that is sensible behavior for expensive queries. It's worth 25ms to check if you can create a smarter plan for specific parameters to a 250ms query.

If your query is run so often that the cost of planning is nontrivial, you can use option (optimize for unknown). That will cause SQL Server to create a plan that it expects to work well for all values of all parameters. When you specify this option, SQL Server ignores the first values of the parameters, so this literally prevents sniffing.

SELECT *
FROM YourTable
WHERE col1 = @par1 AND col2 = @par2 AND ...
OPTION (OPTIMIZE FOR UNKNOWN)

This variant works for all parameters. You can use optimize for (@par1 unknown) to prevent sniffing for just one parameter.