TheSQLGuru (8/7/2014)
I was definitely NOT espousing the use of OPTIMIZE FOR as a SOLUTION for this issue - just to expose it. I DESPISE that "feature", because it GUARANTEES you will get a BAD PLAN for at least some of your executions, potentially many of them!
I apologize. I misunderstood that you were suggesting to try OPTIMIZE FOR to identify the plans for different parameters instead of correcting the issue. While I agree this isn't the best option to solve bad plans caused by parameter sniffing, it may be good enough or it may be the best option. Only testing the different options would identify that.
Re-factoring the procedure to get the best plan is ideal but sometimes not possible.