Skip to main content
Database Administrators

Return to Question

As Mr White suggests, I removed the UPDATE word
Source Link
D-K
D-K
  • 533
  • 1
  • 5
  • 17

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?

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?

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?

Added hibernate
Link
D-K
D-K
  • 533
  • 1
  • 5
  • 17
I went back and re-read the email and realized I have crap for memory. The DBA did NOT refuse. He wasn't a fan...
Source Link
D-K
D-K
  • 533
  • 1
  • 5
  • 17

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 entertainis 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?

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:

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?

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?

Editing to remove useless noise
Source Link
D-K
D-K
  • 533
  • 1
  • 5
  • 17
Removed an update section that was not relevant to the question.
Source Link
D-K
D-K
  • 533
  • 1
  • 5
  • 17
Add links to previous questions. Describe more of the root problem.
Source Link
D-K
D-K
  • 533
  • 1
  • 5
  • 17
Tweeted twitter.com/StackDBAs/status/1385065935611080704
Became Hot Network Question
added a Sp_WhoIsActive @get_locks = 1 information block
Source Link
D-K
D-K
  • 533
  • 1
  • 5
  • 17
tag added
Link
Ronaldo
Ronaldo
  • 5.9k
  • 1
  • 13
  • 43
Source Link
D-K
D-K
  • 533
  • 1
  • 5
  • 17