6

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?

Improve this question

5 Answers 5

Reset to default
20

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.

Improve this answer
2
  • Thanks Dan. I completely agree. It is my hope that, if I get enough feedback from high power DBAs such as yourself - I can help guide us in the right direction. Overcoming unsubstantiated fears.
    – D-K
    Commented Apr 21, 2021 at 16:17
  • I had to use it for a query that we had for which there were three viable plans depending on the value a single parameter took. The wrong plan would drag the system down. Better to pay for a compile with the real values every time.
    – Joshua
    Commented Apr 22, 2021 at 1:46
12

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.

Improve this answer
3
  • Thanks @David Browne. Unfortunately - the query store was never enabled and that might be an even larger hurdle to change. However, I am willing to try given enough evidence.
    – D-K
    Commented Apr 21, 2021 at 16:18
  • @D-K, the cost to implement the query store is very low, but the advantages are huge when it comes to seeing what's going on in the system. ... honestly if the system is so close to the edge that enabling the query store will cripple it, they NEED to invest in more hardware.
    – Jonathan Fite
    Commented Apr 23, 2021 at 14:36
  • 1
    @JonathanFite I can't speak for the DBAs but I am guessing that the real reason this is not enabled, is a mix of "We've never had it before", "What does it do?", "We'd need to push this through a review process (that is long and tedious - explaining to people that have no idea)". That is my guess as to why it is not present and likely will not be enabled.
    – D-K
    Commented Apr 23, 2021 at 15:35
6

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.

Improve this answer
5
  • @D-K Understandable, and my question was somewhat rhetorical as I figured the reasoning would be something of the nature of what you described lol. Best of luck!
    – J.D.
    Commented Apr 21, 2021 at 20:00
  • 2
    @D-K On that note, the community here could probably be of help if you want to post a new question with the query in question and its execution plan. I'm happy to look at what you got going on to see if there's anything else that could be recommended to improve performance.
    – J.D.
    Commented Apr 21, 2021 at 20:12
  • Hey how are you doing J.D.? I wanted to share some info. Read this: dba.stackexchange.com/a/298377/163203 What a ride this has been. I now feel very comfortable with SSIS and the whole ETL process. Great stuff.
    – D-K
    Commented Aug 22, 2021 at 0:49
  • @D-K Hey man, really glad to hear you made some solid improvements to your ETL processes! Bet cutting those runtimes down felt pretty awesome eh? I know I love the feels of it. :) If you need to reach out to me in the future, you can @ me in the "The Heap" chatroom.
    – J.D.
    Commented Aug 22, 2021 at 3:16
  • 1
    I had no idea that this chat room system was in place: chat.stackexchange.com/rooms/179/the-heap--consultancy-
    – D-K
    Commented Aug 22, 2021 at 12:16
2

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.

Improve this answer
3
  • Thank you. I might create another question. Part of the problem is that I am a developer that is learning more and more because I 'want' to solve this. As I peel back the layers of the onion of sql server - my questions (hopefuly) have become more accurate. I started here: dba.stackexchange.com/questions/280998/… and then here: dba.stackexchange.com/questions/289037/… These are all related to ETL and the issue.
    – D-K
    Commented Apr 22, 2021 at 14:08
  • @D-K Apologies, I do recall your second link above where I started to dive into your issue. Unfortunately I got caught up in the busyness of work and life and lost track / forgot to go back to that question. I'm unfortunately still very busy with work tonight, but hopefully I can get some free time this weekend to revisit it and see if I can offer any help. I'll keep that questioned bookmarked so that I can definitely go back and see what I can offer when I get a chance.
    – J.D.
    Commented Apr 22, 2021 at 23:36
  • 1
    @J.D. no worries. I too had life keep me from this for a few days.
    – D-K
    Commented Apr 23, 2021 at 1:00
1

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.

Improve this answer
4
  • thank you. Now I just watched a video from Brent Ozar and in his quirky way he looked at the camera and said ... "SQL Server - doesn't really do caching.". I believe it was "How to Think Like the SQL Server Engine". Perhaps the two of you are talking about different things or I am misunderstanding?
    – D-K
    Commented Apr 22, 2021 at 13:09
  • I don't know what Brent Ozar is talking about, but when one talks about caching in general it is about some type of data caching. SQL has its own multiple cans of worms when it comes to data caching, and I think it neither caches query results nor it caches some intermediate result sets from subqueries. I am talking about reusing the compiled query plans. The SQL Server has (at least for the queries I use) profoundly wrong understanding of what change in the next executed query will make the reused plan 1000x slower. No query hints help, so we simply trade...
    – fraxinus
    Commented Apr 22, 2021 at 13:55
  • ...the recompile time penalty for an adequate plan for the particular query that (hopefully) will get executed acceptably fast. Not that is always helps, ...
    – fraxinus
    Commented Apr 22, 2021 at 14:02
  • AH! Yes - the query plan being cached. My apologies. And yes I agree.
    – D-K
    Commented Apr 22, 2021 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.