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.