NETWORKIO lastwaittype in SQL Server 2000

[billsklar]

We are running several job at a time. The jobs use SQL bulk loaders. We are getting wait times on SQL of upwards of 60-70 seconds for data to be sent to the "client" (in this case that would be the DS appserver). Microsoft came out and said that SQL is basically waiting on DS to accept the next packet of data. This is causing major slowdowns in our jobs. There doesn't seem to be any reason for the delays. The appserver is running at 30-40% and our network can definately handle more traffic. Has enyone seen this before?

Thanks,

Bill

[ogmios]

Try not use the DataStage bulkloaders. Write a job that creates a sequential file and then invoke the bulkloader yourself possible executed via DataStage... in UNIX via scripts, ...

Most of my experience is with the DB2 bulkloader but somehow DataStage butchered their "internal" bulkloaders. Do a comparison of speed between doing it via DataStage and doing it outside of DataStage, you will be amazed.

Ogmios

[billsklar]

Problem seems to be on the "read" side of the job. When I replace the bulk loader with a flat file I still get networkio waittypes. It's like the appserver can't handle the data volume.

[ogmios]

Problem seems to be on the "read" side of the job. When I replace the bulk loader with a flat file I still get networkio waittypes. It's like the appserver can't handle the data volume.

You just proofed yourself it's not the bulk loader causing the delays, I read your original mail wrong.

So what are you trying to do. Read data from SQL-server and then insert it back to an SQL-server database via a bulk loader?

Do some more investigating, if it says it's a "network io wait" you just may have reached maximum capacity of your network. Run e.g. only 1 job at the time, do you then still have the problem?

Ogmios

If you would be reading and inserting to the same database maybe you should consider using "stored procedures" in the end so that data does not need to move over the intranet... (dirty, but if it gets the job done in half the time or less ... )

[billsklar]

Network traffic should not be a problem. We've looked at our network usage through the NIC on both ends and we don't go much past 25% usage. We've also been monitoring overall bandwidth and have not been exceeding any major thresholds there. My next step is to move the job over to the other appserver to see if it is specfic to the machine.

BTW, it is going from one SQL Server to another but they are on different boxes.