Hot answers tagged

19

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 ...


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.


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 ...


2

The connection string is not calling another SSIS Package. The property Application Name is optional filler text so you can differentiate one connection from another. Application Name=SSIS-<Transfer-Now-name of other SSIS package>-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}<IP.db_name.user> If Transfer-Go has the a reference to Transfer-Now in the ...


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 ...


2

The fx glyph indicates that an Expression exists on the item in reference. The first picture indicates there is a an expression on the Precedent Constraint between two items. Whether they're backed by Variables or hard coded won't be known until you explore. The blue color I think is that the preceding task must finish (Completion) - failure or success does ...


1

the provider is the driver name. A connection can be opened against variuos sources and destination (SQL server, Oracle etc..). You have to specify the proper driver. SQLNCLI10.1 is the sql server native client version 10.1 OLE DB Services=-13; is the default option for additional features like pooling and recursion etc... Application Name is only a string ...


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 ...


1

NT SERVICE\MSSQLSERVER is a local service account - therefore it does not have access to network resources (like file shares) Change to either a Network service account or a domain account using SQL Server Configuration Manager The different service account types are described here :- https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/...


1

The Package protection level matters here as a setting of DontSaveSensitive will remove any sensitive data, like a password, when you save the package. You might enter the password, click validate connection and it's good but as soon as you save, it's wiped out and when you re-open, connections are broken because the password is incorrect (blank) There are ...


Only top voted, non community-wiki answers of a minimum length are eligible