How SQL Server determines the data transfer rate

Question

We are having some issues with the data transfer rate when extracting data from SQL Server. Our scenario is as follows:

• SQL Server 2008, in a Windows Server 2003 with 32 GB RAM (located in Guatemala).
• Windows Server 2012 R2, in a EC2 instance in AWS (Virginia USA), with 16 GB RAM.
• For extraction we are using Pentaho Spoon, extracting ~13 tables at the same time.

The issue is because sometimes tables are loaded fast, but sometimes one or two tables are loading very slow. Looks like the data transfer sometimes is really bad for those tables, transferring at 50 rows/s, instead of 2,000 rowd/s (should be normal).

Almost all the times, SQL Server shows in the Activity monitor a Wait Type: ASYNC_NETWORK_IO, which I guess is due the network activity. Even when the data is loaded fast, that wait type is displayed.

How SQL Server determines the transfer rate for each table?

Is it based on indexes or network activity?

The application which is consuming the data, is processing in batches of 5000 rows. It is receiving the data and inserting into a stage database every 5000 rows, even inserting into a flat file. Therefore, I don't think the application is processing the data row by row.

Locally, the performance is better (it's reasonable) but there is always a difference among some tables. Some of them are loaded faster than others. It's weird. I'm not a DBA, so I think it could be something related to indexes? Or something about DB tuning.

Answer 1

The ASYNC_NETWORK_IO wait type indicates that SQL Server has finished doing the work it has been asked to do and is waiting for the consuming application to confirm that it has received all of the data.

This is normally due to the application consuming the data in an inefficient manner. The classic is if the application is processing the data row by row. Another possibility is that the server, that the application is running on, is under performing and causing the application to slow.

With that said it is possible the network is contributing to the problem. A ping from the application server to the SQL Server will show the time it takes to get information across the pipe. If this is near to your wait time then it may indicate your pipe is not fast enough.

The issue is because sometimes tables are loaded fast, but sometimes one or two tables are loading very slow. Looks like the data transfer sometimes is really bad for those tables, transfering at 50 rows/s, instead of 2,000 row/s (should be normal).

At a guess, this sounds like the application is processing the data row by row. The fact that the slow processing is intermittent I would say that it's very unlikely to be the network.