Hot answers tagged

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


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


4

It depends where the SSIS Package is running, but yes the machine or server it's running on is a passthrough between your DB2 server and your SQL server. So the data is loaded into memory of the machine where the SSIS Package is running on, and if you're using certain data transformations and variables, those consume additional memory on the SSIS server also....


3

the first obvious solution could be the adoption of a SQL Server VM hosted on azure. You can move there databases and ssis packages in any traditional ways. This is an Azure Iaas solution and usually is the most expensive. You can reduce cost moving your on prem sql license wiht the "Bring Your own license" solution coming from software assurance ...


3

Dynamic SQL is probably your best bet. See 3rd example here: https://www.sqlservercentral.com/scripts/create-database-snapshot-dynamically


2

I concur with @dan Guzman's comment. You're likely overallocated on memory for SQL Server (and probably for the box itself). Unless you just have continuous package execution and/or suboptimal design patterns in play, that smells like the box will be happy with 1/4 of the resources - just make sure it has the best network speed (assuming data comes from ...


2

I found these tidbits of information while reading through the collation documentation: For example, for the OS locale "English (United States)" (code page 1252), the default collation during setup is SQL_Latin1_General_CP1_CI_AS, and it can be changed to its closest Windows collation counterpart, Latin1_General_100_CI_AS_SC. Reference: Collation ...


2

With the disclaimer that I'm a "dumb American" who doesn't deal with non-English data but did work with a friend recently on using bulk import with UTF-8 data, here's what I see. I have a pipe separated value file that looks like this level|name 7|"Ovasino Poste de Santé" Notepad++ indicates I have saved it as UTF-8. I created two flat ...


2

When you deploy to your SQL Server catalog ensure you have the appropriate drivers installed. If you need the access drivers that include the office drivers to read excel files you can install them from here: https://www.microsoft.com/en-us/download/details.aspx?id=54920 If you are using a 64bit installation of SQL Server you will need the 64 bit driver. If ...


2

A well designed package will never write the data to disk and the data will remain entirely in memory between the source and destination. As J.D. indicates, there are three players in this game: The source, the destination and the machine coordinating the data movement. You could have DB2 LUW running on a windows Server and also host SQL Server on that same ...


2

Either configure the directory to allow Kerberos Constrained Delegation to work or configure a SQL Agent job to run your package with a proxy (or the perhaps just Agent Service Account), and kick off the job with sp_start_job from your applciation.


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

You don't need to use a proxy account nor do you need to grant sysadmin to your ETL team for them to be able to run SSIS packages and SQL Agent jobs. You should first look into the SQL Server Agent Fixed Database Roles, such as the SQLAgentOperatorRole. (I'm guessing you have already, but in case you haven't yet.) If that alone doesn't solve your issue then ...


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


2

Under the default configuration, SQL Agent uses a virtual account, and can access remote resources using the Computer Account (YourDomain\YourServer$). If your server is dedicated to SQL Server, and you don't need multiple identities with different network privileges, then you don't need SQL Agent proxies at all. Just grant the Computer Account the ...


1

Why not limit the data via the query that gets executed, either with the OFFSET and FETCH clauses or with a predicate you can slide the window on with each iteration in SSIS?


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

The dtsx package runs where you install and execute it. Usually in SSISDB. Then you can create a sql server agent job to run it. The connection string specifies where the action (executing some sql script, or as a data source/destination in a dataflow as examples) will be executed. You can install the package in every server you want and then refer in the ...


1

you need two datasources and you have to use the lookup transformation to filter the Server1.


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


1

I wanted to share some of the findings and success stories related to this long running saga. It is amazing what SSIS and ETL can do if you spend the time to learn it. It turns out that OPTION (RECOMPILE) has brute forced the issue and queries that used to take 4 minutes, now take 800 ms. Also poorly thought out hacks to get data from the source DB outside ...


1

We had a similar issue with a query that was called from EntityFramework. Was fast in SSMS, but slow from application. It turned out there was an error in mapping of parameters, their type, which caused the query from application to do a scan, due to the query becomming non-SARGable. After fixing this issue, the query was fast from application.


1

You've answered with your options. Either click and edit each individual annotation or open with your favorite text editor and be prepared to write XML safe comments i.e. runs when sales_amount &gt; 30 Now that I have cracked open a 2017 version SSIS Package, the annotation XML is far friendlier for text editing than it was in the 2005/2008 era. Given a ...


1

You probably want to add Users in the msdb database to the db_ssisltduser role as per the Microsoft Docs on Integration Services Roles. Under the Read and Write Actions section for the db_ssisltduser role it says the following permissions will be applied: Enumerate own packages. Enumerate all packages. View own packages. Execute own packages. Export own ...


1

You can use SUBSTRING to pull apart the source. Then DATEFROMPARTS will stitch them back together. A few notes: dates are not held internally in that format, or any other date-like format. The question says the source is 6 characters but the example shows 8. If you need to find the century you'll have to decide on rules for that.


1

With regards to your idea of downloading the keys, you could setup an initial dataflow of the PKs from ServerB to ServerA into either a permanent staging table or a temp table. Temp table usage in SSIS takes a bit of effort in that you'll want to enable Retain Same Connection on the connection object for ServerA where you'll hold the temp table, initially ...


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