Troubleshooting slow DTS package

Troubleshooting slow DTS package

Post by m » Thu, 23 Oct 2003 05:12:34

Hey All,

Sorry if this group gets a million of these, but I'm trying to figure this
out and I thought I'd ask the experts.

Last weekend, our network guys made "some changes" to the network, but I'm
not sure what and they're not here now. I do know they were trying to
configure the firewall to close all the unused ports, and that they rebooted
all the servers.

So today, my DTS package exporting 16,000 rows to a DBF 4 table which used
to take 22 seconds is now taking 22 MINUTES! This is true even when I'm
logged onto the server under the account it's running under and exporting
locally. I've tried changing the MSDB database to grow 10% at a time
instead of 1 MB at a time, but this doesn't help. I've tried starting and
stopping SQL Server and SQL Agent, rebooting the server, nothing seems to
speed up the package.

An insteresting note: The SQL 2000 servers (one on Win 2000 and one on
2003) are showing this slow performance, but when I run a DTS package on my
7.0 box, it SCREAMS. I'm creating the package ad-hoc each time for
consistency's sake. I'm not using 7.0 mode on the 2000 boxes, and I'm not
using lookups. CPU and pagefile usage is very low on these servers as well.

Can anyone suggest anything else I can try?

Thanks in advance!!!



Troubleshooting slow DTS package

Post by Darren Gre » Fri, 24 Oct 2003 04:20:12

In article < XXXX@XXXXX.COM >, m
< XXXX@XXXXX.COM > writes

Remember DTS is a client side machine, so what ever machine is running
Enterprise Manager is the machine running the package. So for example if
I am sat at my desk and I create a package to load a file from server A
and insert the data into SQL Server on Server B the data is flowing
across the network from Server A to my desktop then onto Server B. In
this case and obvious improvement would be to run the package on either
Server A or Server B. The choice would depend of things like which would
minimise the amount of data being transferred, and also which machine is
best to handle any processing load, depending on what the package is

Since you mention a network change, I would blame the network guys
before I worried about the internals of SQL too much. Make sure you are
running the package in the most appropriate place, and then start
checking your network. Are all NICs set to the correct speed settings?
Are they 100 MB full duplex for example, or are the set to auto? Either
way what does the hardware in between detect? Is the switch registering
that port at the speed you expect?

Networking is a pain, especially when people change stuff and don't
bother to check the impact afterwards. We used to always strap all NICs
to 100 MB Full Duplex, but recently some new Cisco switches just don't
like this, and knock it down to 100 MB Half-duplex. Going against all
previous experience the only way to get decent speed with this kit is to
ensure the NICs are set to auto-negotiate.
Darren Green (SQL Server MVP)

PASS - the definitive, global community for SQL Server professionals


Troubleshooting slow DTS package

Post by m » Sat, 25 Oct 2003 06:10:53

ey Darren,

Thanks for your input. I finally figured it out. It turns out that there
was a change made to one of the datatypes in the table where the DTS was now
trying to export a field with a varchar(1000) datatype. Apparently, DBF
doesn't like this at all. I simply truncated the column's data to something
managable (it wasn't important to get the entire column's data in there,
otherwise I would have just chunked the field and reassembled it later) and
it sped merrily along again.

My first thought was that my Dbase driver was corrupt because I exported to
Excel without issue, but upon slicing up the DTS package's export fields, I
found that pesky long field was to blame.



"Darren Green" < XXXX@XXXXX.COM > wrote in
message news:gGD8YGHshtl$ XXXX@XXXXX.COM ...