I guess my explanation was not succinct. The problem that i have occurs when a query takes longer than usual to execute due to an issue that results from Parameter Sniffing. I have Used option recompile and also Optimize for Local Variable at the same time in different steps in the script.
what would be the best way to handle any issue that results from Parameter sniffing or it can only be resolved on a case by case basis?
How did you figure out that this issue is due to parameter sniffing, did you check the plan xml ?
If the parameter sniffing is the issue, then identify the script that is causing that, the table involved in that script might have skewness, you can try to add option recompile for that statement alone and test.
You have to check the pattern of data in your tables, if skewness is common then you adding recompile option is good.