SSIS 2008 R2 performance and Windows Server 2008 R2 with a clustered SQL instance is slower than when connecting to a non-clustered instance?

5 pts.
Tags:
Business Information Warehouse
SQL Server
SQL Server 2008 performance
SQL Server 2008 R2
SQL Server performance
SSIS
Windows Server 2008 R2
I am testing different configurations for loading data from a remote SQL server using SSIS. In a clustered situation SSIS seems slower to speak to the local clustered SQL instance when compared to a non clustered local SQL instance. It looks like any scenario where SSIS speaks to a clustered SQL instance; the communication occurs through the network regardless if the SSIS instance is local or not. Is this true?

 

In my current scenario, SSIS 2008 R2 is running on a node with clustered SQL Server 2008 R2 instances. It seems to communicate with the clustered instance through some network interface even if they are both on the same Node. Oddly, I cannot identify which interface it is.

 

Here is the general layout:

Cluster Node 1 physical IP 192.168.1.100

SQL Server "sql2008" instance name has an IP 192.168.1.101

SQL Server Instance is on Node1

Remote SQL server source for SSIS IP 192.168.1.55

 

The SSIS Load transfer rate seems slow. The SSIS network session to the remote SQL server source appears under the Network section in Resource Monitor.

 

Image: DTExec.exe

PID: 8864

Address: 192.168.1.55

Send (B/sec): 968

Receive (B/sec): 1,682,022

Total (B/sec): 1,682,990

 

But the SSIS for loading also appears in network. It seems to run from the local node to to the destination SQL server IP through network communication because DTexec shows up under Resource Monitor - Network as speaking to the Destination SQL server IP.

 

Image: DTExec.exe

PID: 8864

Address: 192.168.1.101

Receive (B/sec): 120

Send (B/sec): 20,300,868

Unfortunately when I use network monitor to see the traffic, the dtexec traffic from source appears but the load activity does not seem to appear to be using any of the network interfaces and cannot be seen.

When running this same package (against the same unchanged source) with a less powerful non clustered box, the SSIS package runs 2x faster speaking to a local instance and the Dtexec load process does not appear in the Network section in Resource Monitor. Only the Dtexec network session for the source process appears and is the same speed.

Is the missing Network traffic on the non clustered scenario giving me the boost or am I looking at something misleading? 





Software/Hardware used:
Windows 2008 R2, SQL Server 2008 R2, SQL Server Integration Services 2008 R2, IBM XIV, IBM x3690 X5

Answer Wiki

Thanks. We'll let you know when a new response is added.

If SSIS and the SQL Instance are running on the same physical server then the connection should not be going out over the network. If they are on different nodes then the connection will go over the network.

By default connections made that don’t leave the server are made over the shared memory protocol driver. In a clustered setup this isn’t always true however because if SSIS attempts to talk to SQL Server from a different source IP address than the one that the SQL Server is listening on then the Shared Memory protocol can’t be used. In this case TCP/IP will be used and the connection will go as far as the TCP stack on the server which will redirect it to the SQL Server’s IP Address without the network traffic going out to the network.

Because you are using TCP to connect instead of shared memory and TCP is slower than shared memory the clustered connection may be slower.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following