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.
- 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?
- 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
serializablewithin the hibernate layer; which I have learned is not optimal for high volume production environments.
Let me share the other questions: