Parameter sniffing work arounds
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.
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.