The ASYNC_NETWORK_IO wait type is one of those wait types that can be seen very often by DBAs, and it can be worrisome when excessive values occur, as it is one of the most difficult wait types to fix.

It is important to know that ASYNC_NETWORK_IO name is adopted starting from SQL Server 2005, while in SQL Server 2000 this wait type is known as NETWORKIO. The original name of this wait type originates from the period of the slow Ethernet speeds of 10 Megabits and 100 Megabits that are commonly in use until the mid-2000s

In most cases excessive values for this wait type are not actually related to any network issues (or it is a very rare case), especially in today’s very fast Ethernet speeds of 40 Gigabit or 100 Gigabit, and those of 200 Gigabit and 400 Gigabit speed that are under development at the moment.

Excessive ASYNC_NETWORK_IO waits could occur under two scenarios:

The session must wait for the client application to process the data received from SQL Server in order to send the signal to SQL Server that it can accept new data for processing. This is a common scenario that may reflect bad application design, and is the most often cause of excessive ASYNC_NETWORK_IO wait type values

Network bandwidth is maxed out. A clogged Ethernet will cause the slow data transmission back and forth from the application. This, in and of itself, will degrade the efficiency of the application.

A problem with the client application

The most common reason for excessive SQL Server ASYNC_NETWORK_IO wait types is that the application cannot process the data that arrives from SQL Server fast enough. When an application requests large data result sets, slow data processing will cause data buffers to be filled, thus preventing SQL Server from sending new data to the client. Row by Agonizing Row (RBAR) processing is often the cause of such behavior and high ASYNC_NETWORK_IO wait type values. In RBAR application programming, only one row at a time is processed from the result set sent by SQL Server. In such a scenario, the complete result set, available for processing, is cached and then SQL Server is notified that the data set has been “processed”. This will allow SQL Server to send a new data set while the application is processing the data from the cached results set

When an application that is using the RBAR processing is forced to work with a very large database environment (VLDB), it will often encounter issues in processing data. The server process (SPID) that is executes the batch will be forced to wait until the application manages to start processing the data stored in the buffer allowing SQL Server to send the new result set to the client (via buffer). And while waiting to send a new data requested by the application to a buffer for further processing it generates the ASYNC_NETWORK_IO wait type.

So what DBAs can do when they encounter high ASYNC_NETWORK_IO wait type values on SQL Server? This involves investigating the application that is causing the excessive ASYNC_NETWORK_IO wait type values and often the coordinating with the application developers who created it. While investigating the excessive ASYNC_NETWORK_IO wait type values, the following should be checked

1. Check whether the application is requesting large data sets from a SQL Server instance, and then if it filters those data on the client side. Pay attention to third-party applications like Microsoft Access or ORM software (aka Object relational mapping) for example, that may be requesting the large data sets that they are filtering on the client side. Using the read immediately and process afterwards programing method may often save users from excessive ASYNC_NETWORK_IO wait type values

2. Make sure that appropriate views are created for the client application, as this can ensure that data filtering is done by the SQL Server instance and therefore the significantly lower amount of data will be send to the client application

3. Make sure that the application is committing the opened transactions and that it committing them in a timely manner

4. Check if there is the way to reduce the requested dataset in a way to perform data filtering on the SQL Server directly

5. In case of individual or ad-hock queries, make sure that WHERE clause is added wherever it is possible and that query is properly optimized in a way to restrict the requested data set to only the required data

6. Check if it possible to use “TOP n” in the query to decrease the row number that will be returned by the query

7. Scalar-Valued User Defined Functions (UDF) are often the cause of the high ASYNC_NETWORK_IO wait type due to RBAR, so look for any instances of these objects that may be affecting performance

8. Using a Computed Column Defined with a User Defined Function (UDF) with a large database is another frequent reason for the high ASYNC_NETWORK_IO wait type due to RBAR

9. In case of SQL Server 2016, it is possible to use natively compiled UDFs that can significantly lower RBAR in most cases and to improve the execution speed up to 100%. This can be particularly useful in situations when refactoring UDF to a Table-Valued Function is not an option

Note:
SQL Server Management Studio is an infamous client application for its reputation of generating ASYNC_NETWORK_IO wait type. SSMS reads the data stream in one row at a time and dealing with each row before retrieving the next row

In addition, there are some other things that can be done by tweaking of SQL Server directly when it is needed to deal with situations when excessive ASYNC_NETWORK_IO waits values are encountered even when during the huge data loads processing on SQL Server side:

• Enable Shared memory protocol for that SQL Server instance if it is not already done
  Use the following query to determine the protocol used for the current connection:

  1 2 3 4 5

  SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;

• Make sure that client is connected using net_transport=’Shared memory’

If everything above is checked and SQL Server is still hit by high ASYNC_NETWORK_IO wait values, then it is the time to check potential network related issues that might cause such behavior. There are different causes that are generally caused by physical network limitation, malfunction or simple because of wrong network setup. The following should be carefully inspected in order to troubleshoot the network caused ASYNC_NETWORK_IO waits

Problems with the network

• Check network bandwidth between the SQL Server and client. Slow network adapters with bandwidth that does not correspond to the estimated amount of data that should be processed on the client side is the often reason for high ASYNC_NETWORK_IO waits values. 100 Megabits adapters are still present and they often cannot answer to demands of modern SQL Server databases and the amount of data processed. Even switching to 1 Gigabit adapters still leaves the system below current requirements in many environments. Using 10 Gigabits network adapters is something that is considered as a minimum for most environments, while 200 Gigabits and 400 Gigabits are something that many enterprises will have to switch in the near future, if they didn’t do that already

• Make sure that all network components between the SQL Server instance and the client, such as routers, switches, cables are properly configured, fully functional and dimensioned according to required bandwidth

• Review the Batch requests per second counter values, as this could often indicate the reason for high ASYNC_NETWORK_IO waits. When Batch Requests are examined, what has to be examined is the number of T-SQL batches processed by SQL server since this is what will determine the number of batches SQL Server is processing per second. Servers with Batch Requests per second value larger than 1000 are considered as “busy”. The recommended value could be heavily dependent on the actual system configuration, activity level, and number of transactions being processed. It is not uncommon that this value can be significantly higher during peak hours

  When the Batch requests per second counter value is close or larger than 3,000 is encountered on a 100 Megabits network, this is almost certainly an indication that network speed is the bottleneck and resulting in the high ASYNC_NETWORK_IO waits values. With servers hitting easily over 20,000 Batch requests per seconds in these days, it is smart to consider upgrading 1 Gigabits or lower networks to 10 Gigabits to meet the increasing demands for SQL Server data processing

• Checking the NIC bandwidth utilization is prudent, even often overlooked.

  Using Perfmon it is easy to calculate the network utilization via the formula:
  Network utilization %= ((Total Bytes\Sec * 8)/current bandwidth) * 100
  

  If values are larger than 60% on regular basis, switching to a faster network adapter/network bandwidth is highly advisable in order to ensure that enough bandwidth can be allocated when needed for data processing

• Make sure that Auto Negotiate of the NIC is detecting the network bandwidth properly

  To check the current speed of all active network connection, use the following CLI command

  wmic NIC where NetEnabled=true get Name, Speed

  

  In case that Auto negotiation for a specific adapter is not picking the correct network speed, it is possible to set up the NIC speed manually in the NIS properties

  

• Author
• Recent Posts

Nikola Dimitrijevic

Nikola is computer freak since 1981 and an SQL enthusiast with intention to became a freak. Specialized in SQL Server auditing, compliance and performance monitoring.

Military aviation devotee and hard core scale aircraft modeler. Extreme sports fan; parachutist and bungee jump instructor. Once serious, now just a free time photographer

View all posts by Nikola Dimitrijevic

Latest posts by Nikola Dimitrijevic (see all)

• SQL Server trace flags guide; from -1 to 840 - March 4, 2019
• How to handle the SQL Server WRITELOG wait type - June 13, 2018
• SQL Server performance counters (Batch Requests/sec or Transactions/sec): what to monitor and why - June 5, 2018