It sometimes makes sense to add OPTION (RECOMPILE) to a query. Typically this will be when:
- A good enough plan for the query is very sensitive to one or more parameters
- No good single value exists for the parameter to use in a hint
- Optimize for unknown doesn’t give a good result
- The plan might be expected to change over time
- The cost of recompiling the statement is much less than the expected execution time
- Recompiling every time is very likely to save more time and resources than it costs overall
All that is fairly well-known. The point of this short post is to draw your attention to another side-effect of adding OPTION (RECOMPILE) — the parameter embedding optimization (PEO).