Is OPTION (RECOMPILE) used in production environments?

All we need is an easy explanation of the problem, so here it is.

Is OPTION (RECOMPILE) used in production?

This option seems to get a lot of bad press. Is it deserved?

I have a DBA who, so far, is not a fan of the idea of OPTION (RECOMPILE) within the meat of Report ETL ssis agent queries. These queries are executed (to the best of my knowledge) sequentially and at scheduled intervals.

Back History:

  • SQL Server 2016
  • ETL Queries that cause clustered index scans when run through the ssis agent. These queries take minutes to complete and cause heavy impact.
  • The same query and parameter run via a local stored procedures executes in less than a second.

Wait are you certain OPTION (RECOMPILE) is the answer?

  • Unknown.
  • But I need to know whether this is a really bad idea before I try.

The risks I’m aware of:

So given the above – is this option actually used in the real world? Is it acceptable that I recommend (and test) this as an option for a production environment?


I was asked to provide more details. I mentioned that I do have other posts all related to this topic. Let me give more information on that:

  • The root problem is that queries coming from an application server
    are taking longer than 60 seconds. Normally these queries take 4 to
    10 seconds. Through a lot of pain, I’ve determined that the time outs
    line up with the ETL queries. 4 queries out of 15 to be specific.
  • A contributor to the problem is found within the application servers. Specifically the isolation level is set to serializable within the hibernate layer; which I have learned is not optimal for high volume production environments.

Let me share the other questions:

SQL Server – Can I surgically remove a bad cached query plan or am I chasing the wrong idea?

Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

OPTION(RECOMPILE) is used in real word production scenarios. I’ve employed it to address parameter sniffing and optimize kitchen sink queries. It might be the answer for your issue but the symptoms suggest OPTIMIZE FOR UNKNOWN (same as local variables) may address the problem as well.

I certainly would not avoid an option just because a bug once existed, and it was fixed several years ago. The main risk with OPTION(RECOMPILE) is when it’s used inappropriately, such as high-frequency queries.

Method 2

Yes. OPTION RECOMPILE is appropriate for high-cost/low-frequency queries where the query cost varies significantly by parameter values. As an alternative consider using the Query Store, where you can force a good plan.

Method 3

As the experts mentioned, there are certainly valid use cases for the OPTION (RECOMPILE) query hint in production code. It’s just not usually the only or best solution and can make your performance problems worse when incorrectly used, especially on high frequency queries.

That being said, conversely sometimes there are situations where there is no other fix other than a query hint like OPTION (RECOMPILE) and it is the solution to a performance problem. We can’t say if your SSIS query is an example of this without a lot more details, but just a general thought I wanted to throw out there for your DBA, because just like Siths, it’s bad to deal in absolutes. 🙃

If you had a case where the only solution was OPTION (RECOMPILE), I’d be curious on what your DBA’s response would be on how to solve the problem. Furthermore you should ask your DBA what his thoughts are on solving this problem without testing OPTION (RECOMPILE). Because generally it’s a low-risk test, and can be easily removed should it hurt performance worse than it helps. I’ve even found it helpful in a performance bind when there isn’t time to solve the root issue so it bought breathing room in the interim.

Long story short, query hints have a notoriety of being risky because there’s a history on people using them incorrectly. But they exist for a reason, when they are the right tool for the right job. The risk of using OPTION (RECOMPILE) is low when closely monitored and is even the only solution in some contexts.

Method 4

If I might recommend an alternative solution based on a comment by J.D.:

Post the query on this site along with the execution plan and ask if any of us see any obvious issues with the query. Assuming your query does not contain any confidential data, obviously. Even if OPTION(RECOMPILE) would help query execution significantly, it’s possible there are other effects from running this through a stored procedure that you might not achieve through the Recompile option.

Method 5

I have seen usage of OPTION(RECOMPILE) in production quite a lot.

It looks like the query plan caching mechanism is not very clever, but it pretends to be clever so it likes to override the developer’s understanding of the plan reusability in different contexts.

A big, big query recompilation can take like 20-50ms and a bad plan from the cache can convert a sub-100ms query into a minutes-long cpu hog. In this regard, OPTION(RECOMPILE) looks like a good deal.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply