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?
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.