I have a process I built in Powershell which has been working flawlessly for years on a couple servers, but when I migrate it to new servers I see a lot of waits (ASYNC_NETWORK_IO) during BCP step resulting in work taking much longer.
Synopsis of the process: A scheduled task starts a PS script I call the thread manager. This script calls another script that does the actual work using start-job. The script doing the work is selecting a zip file with archived data, unzips it, BCP's it to a database. There could be up to 15 threads running at a time. The task/script, DB's, and files as they are being work all exist on the same server. While multiple threads could be bcp'ing to a single DB each thread will have its own table to import to.
Server 1: As stated, this has worked flawlessly for years. Physical server, regular drives for storage, SQL 2012.
Server 2: The new server seeing waits. VM, mount points, SQL 2019
A 30 GB file should take about 15 minutes to import. I have some threads sitting for over a day. Observations: If there are 10 threads going, only 2 or 3 will have any CPU use Those same two or three will be the only ones passing any kind of data on the network tab of Resource Monitor The BCP utility log will show no activity for an hour then have a spurt of activity where a few hundred thousand rows get inserted 1000 at a time. ASYNC_NETWORK_IO waits in SQL Server No blocking
I am a DBA so can more easily answer questions about what I'm seeing on SQL Server. I can log into the server and look at built in OS monitoring, but have no access to the VM Ware console or storage, so if anything needs to be validated there I will need to pass it along.
TIA!
