I have used two methods to get around parameter sniffing issues in the past:
1) Use WITH RECOMPILE
2) Reassign parameter values to local variables and use those instead of the parameters
From what I understand, the end result of both of these is the same - a new execution plan that is optimized for the current query/params is created and used.
If this is true, are there any differences between these two methods or are they essentially the same? Is one preferable over the other?
If you can anticipate typical values, run your stored procedure with such values whenever you (re-)create it. An execution plan will be created based on these values, and stored for future use. Most of my stored procedure .sql files end with an EXEC command with reasonable values, for exactly this purpose (that, and to identify basic errors in the code).
Of course this won't help if your stored procedure is very long running, enough that you can't reasonably execute it during production hours. Note that it's not enough to start the job, it has to complete successfully for a plan to be stored.
WITH RECOMPILE
This is a bit of a sledgehammer. It recompiles every statement in the module. Using OPTION (RECOMPILE) on the statements that are parameter-sensitive is a more targeted solution.
- Reassign parameter values to local variables and use those instead of the parameters
This has the same effect as OPTIMIZE FOR UNKNOWN i.e. a plan is generated based on average values. The plan does not recompile on each execution.
For more information please see my article Parameter Sniffing, Embedding, and the RECOMPILE Options.
