You may occasionally see ASYNC_NETWORK_IO waits surface in your database . This wait can be caused by an unhealthy network connection; however, more often than not I see it caused for different reasons.
What are causes of ASYNC_NETWORK_IO wait?
There’s a few common scenarios I see as a cause for this.
- Queries retrieving large result sets over a slow connection
- Applications which open large result sets but take their time consuming the result
- Busy application servers causing slow consumption of a result set
- Network issues
Large result sets over a slow connection
A common scenario for this is tunneling to a database over a slow VPN connection and retrieving large result sets. I typically spot this scenario by making note of the user id’s involved and the program name. If I see it is end users and they are connecting with something like SSMS from their workstation this can be queries being run over a wide area network pulling large result sets. Should this scenario be concerning? In some cases yes. I have seen where a query in ASYNC_NETWORK_IO wait held shared locks on resources ultimately blocking other processes. The moral of this story is use nolock hints if you intend to pull large result sets if it’s just for support purposes.
Applications that take their time
The biggest offender of this is Microsoft Access connecting to SQL Server. There are some controls in Access that allow you to flip through a result set a record at a time. This control executes a query on SQL and only retrieves the first row. The result set may consist of 2000 rows. By only consuming the first row it prevents SQL from sending additional rows. To some extent the data will buffer on the TCP stack so this doesn’t affect small result sets; however, with large result sets the TCP buffers will fill and sending the results from SQL to the client will queue up – at this point SQL will report this time as ASYNC_NETWORK_IO. This scenario will show up in network traces as TCP zero window events in a network trace.
Busy application servers
On occasion I will see a query that typically returns quickly with no waits start reporting ASYNC_NETWORK_IO wait. This is somewhat similar to the scenario I mentioned above; however, in this case the slow consumption of the result set is related to a resource constraint on the consumer. Review the resources of the consumer in these scenarios. Is the consumer (ie the application server) at 100% CPU usage? Is the consumer memory constrained?
Network issues
I mention this last because this doesn’t seem to surface as much as the other scenarios I previously mentioned, but it does happen! If you are comfortable with using tools such as wireshark, netmon, or netsh you will see high TCP retransmissions occurring between SQL and the affected clients.
There are only four causes for this wait type and none of them are SQL Server:
1. Network issues – dropped packets, slow network, etc.
2. Application does not pick up all the results – there is a setting or limitation to the amount of results the application will pick up at a time.
3. Result set too large – the query the application is running is creating a result set so large that it is impossible to pick up everything in one trip.
4. Performance issue on the client – the requester of the data is experiencing resource contention issues, causing a delay in the ability to pick up the results in a timely manner.
Async Network I/O means that SQL has run the query and has the results, but the results are not getting picked up quickly enough. When the results sit there, nothing else can run until those results are picked up.