1

I'm running SQL Server Integration Services (SSIS) packages in SQL Server agent every day. I've been monitoring the performance of the packages and see a lot of network activity in Resource Monitor. I'm loading data from a local SQL Server instance to another local SQL Server instance. However, I use fully-qualified URLs to address the servers b/c it makes developing offline much easier. So, instead of 10.1.2.X I use data.mydomain.com\instance.

Am I taking a performance hit by using the external URL rather than referencing the database as 10.1.2.X\instance or localhost\instance? Also, why am I seeing such high network traffic while these packages run? There's really nothing that should be going across the network.

Here's what my network traffic looks like in Resource Monitor while a job runs a package.

ssis package network activity

Obviously there's a lot of traffic moving around on the network, but the job that is running only reads and writes data from the same machine.

Thanks!

Kevin Babcock
  • 243
  • 1
  • 4
  • 12

2 Answers2

2

Assuming these "external URLs" are actually resolving to local addresses, and again assuming that you don't have any DNS issues, no, this really shouldn't make a difference at all.

As for high network activity, what exactly are you seeing?

EDIT

I'm taking a wild stab here (as I know very little of SSIS and the internal workings of MS SQL in general), but perhaps the DTEXEC.EXE process runs as the NETWORK SERVICE and/or uses the loopback adapter for local processing?

You can always verify whether it's real network traffic or not by running a capture with Wireshark or using a simple bandwidth monitor like NetWorx Bandwidth Monitor (there's a portable version to download; no install necessary, freeware) on the SQL server. Actually you could probably run permon and setup some network counters but if my stab in the dark is correct, this could show the same results.

gravyface
  • 13,957
  • 19
  • 68
  • 100
2

If both instances are on the same system, the only performance "hit" you'll see when using the fully-qualified domain name is for the DNS lookup. Assuming your DNS server (or hosts file) remains accessible, the performance impact of this will be negligible.

IP will use the system's loopback interface when accessing those FQDNs, so the external network won't actually get involved in traffic destined for localhost (and any URIs that end up there).

yith
  • 71
  • 4
  • that's kind of where I was going (with relative uncertainty). However, 955MB/minute is pretty heavy for just DNS lookups. – gravyface Jun 01 '11 at 11:15