CODEWITHSUNDEEP
sql-serverssissql-server-2012
Vaccano
Vaccano

Reputation: 82291

Is SSIS faster if run from the server that the data is on?

Say I am copying data from Database A to Database B and both of them are on the same Sql Server. I am taking data from a table and putting it into another table (no transforms).

Does the performance improve noticeably if I run the SSIS package on the server that hosts the databases? (As opposed to running it on a dedicated SSIS server.)

Upvotes: 1

Views: 1873

Answers (2)

Bacon Bits
Bacon Bits

Reputation: 32145

If your SSIS task is connecting directly to the remote server and doing nothing more complex than doing Execute SQL Tasks and running queries of the type:

INSERT INTO Database2.dbo.DestinationTable (<field_list>)
SELECT <field_list>
FROM Database1.dbo.SourceTable
WHERE <criteria>

Then as far as I know it's not going to matter. SSIS is just acting like any other SQL client.

If you're using a Data Flow Task, however, then it may have an impact. If you're using DFTs, then you're still required to load all the data into the SSIS client and then insert it all back to the server whether you're running locally or remotely. There's quite a bit you can do with data buffers to greatly increase the efficiency, but it's certainly possible that the bottleneck for I/O is the network instead of the disk read and write speeds.

That said, given the contention for CPU and memory on SQL Server and with SSIS and streaming large amounts of data, I would not be surprised if you saw a performance gain in some instances simply because you're spreading out your resource demands. If you're bottlenecking on CPU and RAM, then running on a second server is actually great.

One caveat: If you require transaction support (TransactionOption = Required), then the standard way to do that on a remote connection is with MSDTC, and in my experience that can have a noticeable performance impact due to the overhead. There are alternative ways of doing that, though.

Upvotes: 1

Bobby
Bobby

Reputation: 76

Best practice suggest that you separate your ETL Server from Database server. Reason is simple... ETL Processes can be memory hungry and certain SSIS executions can slow down your core database performance and give DBA headache for sure ... SSIS data flow transforms, lookups, joins all are in memory transforms and use RAM heavily... with that said its not a good idea to run SSIS on the same server unless you have budget constraint or some other challenges.

Similar answer on Stackoverflow. To learn more about SSIS performance best practices check this blog

Q: Does the performance improve noticeably if I run the SSIS package on the server that hosts the databases? A: I guess yes but depends on your network topology. But if I were you and I have to copy data from DatabaseA to DatabaseB on same server instance then I would simply do below (use execute SQL task rather than Dataflow)

select * into DatabaseB.dbo.Table1 from DatabaseA.dbo.Table1

Upvotes: 1

Related Questions