Performance running DTS jobs
-
Hello to all. I hope some one can enlight my already fried head to figure out what could be the problem on one of the SQL servers I kind of administer. This is the case:
This particular server runs about 40 to 50 DTS packages on nightly basis (all a little complex, but running fine for the past few years) All of the DTS were runing fine until a couple of weeks back.
The DTS are taking longer that they used to, but (here is the catch!!) it is not consistent when this happens. For a few days the server runs all the DTS as before, then for a couple of days it slows down to an unbelievable time. Just as an example, a job running in good days in about 45 minutes today it took 4 hours and 15 min!!! Of course this cause an overlapping with other jobs casuing a delay almost impossible to handle. After a couple of days, the server goes back to normal and process all DTS in the regular time.
I checked event viewer, profiler, server performance, etc. At this point, I am clueless!!! Well nothing new there!! Is there any reason why this is happening? how can I check the reason behind this?
I am afraid that this is just a signal that something is absolutely wrong with the server and it is letting me know "backup everything and move everything to a different machine because I am sick and I amj going to crash!!!!)
Other that, the server is fine. The access to the data stored is as usual. No other tasks run in the background, antivirus running and up-to-date. Please HELP!!!!
-
What are your packages doing?
We have 30 odd packages that run on a nightly basis that extract data from a Unix based Progress database using ODBC.
The timings for our extracts can fluctate by as much as 90 mins. We've found that other processes running on the Unix machine have an impact on how fast the data can be extracted.
Unfortunatly the Unix machine is managed by a third party and we don't have any control over what and when things are run on the machine.
--------------------
Colt 45 - the original point and click interface
-
remember dts runs as an external application and running lots of them together may shrink the available resource on the server.
If you're moving data across the network then that may be a factor.
Other jobs running that contend with the disk usage may slow things, as will jobs that overlap - if you're running multiple jobs accessing the same drive array despite it being an array it can still only read or write once at a time per disk.
Only other thing I've seen affecting dts exports/imports is out of date stats and indexes which shouldn't be there or should be there, depending on if extract or import. Of course if the data set has changed that could be an issue.
I think it highly unlikley this is a warning of a server about to crash - check your event logs as well as sql logs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I agree. We have nightly extracts that pull from DB2 and the time necessary to extract the same data really varies. Some nights it can take as little as 1.5 hrs and then other nights, it takes 15 hrs. It's all due to the load on the DB2 server and what other processes are running there. It has never been a problem with SQL Server and the DTS. Try to determine what other processes are running and schedule around them if possible.
-
Thank ypou for your input to al of you. I get the files via FTP from offices around the country, then the files are moved to the data server (SQL Server machine) and run locally using ODBC for the type of files received (BASIS)
I have checking the performace screen and the writes and reads are as always has been. It is a RAID 5 and I am assuming this could be one of the reasons the DTS packages are sometimes a little slow, but after almost four years runniing and just now start to happen? I will move some of these jobs to a different server to see if that balance a little bit better the load to that server.
In any event, I would like to hear more suggestion from you guys. This site has always help me find the answers to many problems in the past. Thank you
-
FWIW -
You might want to use Performance Monitor to monitor memory usage. Your variability in execution time may be reflected in memory and/or thread usage by DTS. We use DTS to load data from various systems into a data warehouse running on SQL Server and had performance issues and ended up moving the various DTS jobs to multiple workstations. SQL Server was never the issue, but the lack of resources on an individual machine sure was.
Paul Westphal
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply