Note: For SQL2000 this wait is called NETWORKIO.
The ASYNC_NETWORK_IO wait indicates that one of two scenarios are happening. The first scenario is that the session (i.e., SPID) is waiting for the client application to process the result set and send a signal back to SQL Server that it is ready to process more data. The second is that there may be a network performance issue.
Reducing SQL Server waits / wait times
If there are significant wait times on ASYNC_NETWORK_IO you have the following options:
- Review the queries and identify large result sets. Verify that the client application is consuming data as efficiently as possible. For example, if the application is asking for a million rows of data but only processing one row at a time.
- Review that all rows being requested are necessary. Often times it is the case that you can reduce this wait by filtering the result set for only the rows that are needed. Using the TOP clause may be an option as well. Client applications such as Microsoft Access may benefit from querying a view instead of pulling data from an entire table.
If the above tuning tips are reviewed and applied, but the server is still encountering high wait times, then ensure there aren’t any network-related issues:
- Validate the network components between the application/clients and the SQL Server instance (router, for example).
- Look at your NIC configuration on the server to make sure there are no issues with the physical card. Also, check if autodetect is picking the fastest speed.
- Check network adapter bandwidth: 1 Gigabit is better than 100 megabits, and 100 megabits is better than 10 megabits.
Also worth mention is the common practice of performing data loads on the server. It is possible that you may be seeing the ASYNC_NETWORK_IO wait during the times that the data loads are occurring.If this is the case then make sure the shared memory protocol is enabled for the SQL Server instance and the session is connected using net_transport = ‘Shared memory’. You can determine the net_transport for the connection by looking at the DMV – sys.dm_exec_connections.