Unanswered: SQL Server 2000 SP4 slows down dts job
We have just upgraded to Service Pack 4 on our SQL Server 2000.
We have had a DTS job that normally takes about four hours to complete(this dts job has been ok for the last three years).
However, after applying SP4, this DTS job now takes over 8 hours to complete.
There are no other processes running on the box and the box is a high end Dell machine with 8 Gig of RAM.
....there are over 60 tranformations in that DTS package.
The guy who wrote it now runs his own company and is doing very well for himself!!! I pity his clients though.
This big thing is due to be replaced in the next few months, its a big load which takes data from dataflex, shoves the info into SQL tables and then the big job rips out all of the data using joins into more understandable tables.
The whole thing takes over 9 hours....
We have applied the hotfixs and the job is running even slower now.
We will be looking at optimizing some queries. But the problem is still down to spid latches. Any advice on the latch issue would be helpful.
FOUR hours! I just find that very hard to believe.
I advise you run each step manually and see which ones take longer than a few minutes (or ok, maybe just the top 5-10 ), then report back with how long it takes and what the badger is trying to do. We can't help fix a problem blind, can we?