Sniffing parameter
Let's say we have a poorly performing stored procedure with 6 parameters. If one of the six parameters is transferred to a local variable in a stored procedure, is it enough to turn off the sniffing parameter, or is it necessary to pass all 6 parameters that are passed to the stored procedure to local variables in the stored procedure?
source to share
Per Paul White 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 obfuscating the parser about the parameter value, so in order for it to work for each parameter, you would need to store each parameter in a local variable.
More recent versions of SQL Server have better solutions. For an expensive query that doesn't get executed often, I would 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") the plan each time the stored procedure is called. Given the low scheduling cost (typically below 25ms), this is reasonable behavior for expensive queries. It costs 25ms to check if you can create a smarter plan for specific parameters for a 250ms request.
If your query is being executed so often that the scheduling cost is non-trivial, you can use option (optimize for unknown)
. This will force SQL Server to create a plan that is expected to work well for all values of all parameters. When you specify this parameter, SQL Server ignores the first parameter values, so this literally prevents sniffing.
SELECT *
FROM YourTable
WHERE col1 = @par1 AND col2 = @par2 AND ...
OPTION (OPTIMIZE FOR UNKNOWN)
This option works for all parameters. You can optimize for (@par1 unknown)
only use one parameter to prevent sniffing.
source to share