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, refuses to entertain 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?
UPDATE
I was able to get a bit more information. A support engineer with admin rights executed this for me:
Sp_WhoIsActive @get_locks = 1;
I am still reading documentation to understand what I'm seeing here. The 'who' was executed during one of the long running ETL queries and that block is from the offending ETL query row.
UPDATE #2
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 that the application servers have a legacy setting that I pushed for out of ignorance - years ago. The wildfly hibernate layer set the isolation level to 'Serializable'. I've come to learn that is not a great idea. In the phase #2 of my proposal to fix this - I am removing this setting from the application server.
I'm getting to far into the weeds there. 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?