About This Blog

Including my content from SQLBlog.com and some from SQLPerformance.com

Thursday, 18 November 2021

Be Careful with LOBs and OPTION (RECOMPILE)

Be Careful with LOBs and OPTION (RECOMPILE)

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).