SQL Server – Is OPTION (RECOMPILE) Used in Production Environments?

hibernateperformance-tuningsql serversql-server-2016ssis

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?

UPDATE

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?

Best Answer

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.

Related Question