Is OPTION (RECOMPILE) used in production environments?

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:

• There have been at least two serious bugs in the past related to

OPTION (RECOMPILE). Two queries run at the same time could swap
result sets. Ugh !!
https://support.microsoft.com/en-us/topic/fix-rare-possibility-of-incorrect-results-when-you-use-option-recompile-for-queries-inside-a-procedure-in-sql-server-2014-or-sql-server-2012-c247fbb5-4125-dd0f-7789-7b1c126f8241

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 S

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.

StackExchange Database Administrators Q#290210, answer score: 20