Unanswered: Need Help, DTS is Running extremely Slow!!!
I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).
The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.
The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.
by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status
i have tried everything and cant seem to get this problem fixed
Does anyone know what could be the problem?
I Need Help Quick!!!
To maximize CPU resources, you can define a CPU idle condition for SQL Server Agent. SQL Server Agent uses the CPU idle condition setting to determine the most advantageous time to execute jobs.
For example, you can schedule a daily backup job to occur during CPU idle time and slow production periods.
Before you define jobs to execute during CPU idle time, determine how much CPU the job requires. You can use SQL Server Profiler or Windows NT Performance Monitor to monitor server traffic and collect statistics. You can use the information you gather to set the CPU idle time percentage.
Define the CPU idle condition as a percentage below which the average CPU usage must remain for a specified time. Next, set the amount of time. When this time has been exceeded, SQL Server Agent starts all jobs that have a CPU idle time schedule.
The are a number of thing you can do. It's specific to your job. First of all who has control. AS400 or the database(SQL). I don't know of a DTS package in AS400. So start there, next, you might want to look at the parameters manually vs job. Are there any environmental issues to look at. Such as: numbers of records to commit, indexes, maintenance and backups, buffers size, Look it up on books online SQL Server.