大网数据广告活动
数据库与应用安全

Troubleshoot slow queries resulting from ASYNC_NETWORK_IO

· 52CE 我爱测

Provides resolutions for slow query issues that result from ASYNC_NETWORK_IO wait type.

Symptoms

When SQL Server produces result sets and sends them to a client application by putting the results in an output buffer, the client application fetches them from the output buffer. If the client application stops or doesn't fetch the results fast enough, SQL Server has to wait for acknowledgment that the client application has received all the results before sending more results. This wait will show up as ASYNC_NETWORK_IO. For more information, see the video at Understanding ASYNC_NETWORK_IO Waits in SQL Server.

Excessive ASYNC_NETWORK_IO waits can cause two issues:

  • Queries may slow down because their total duration will be longer.

  • When SQL Server waits for the client to fetch results, it can't release locks acquired. If the lock isn't released for a long time, other sessions will be blocked on SQL Server.

Causes and resolutions

The following sections list the common causes for this wait type and the corresponding steps to resolve the issue:

Large result set

Some application clients request thousands or even millions of rows and then process the results by applying filters, sorting, and aggregations. Large result sets may lead to unnecessary network utilization and client application processing.

Resolution: Application developers must carefully balance the processing between SQL Server and clients. Filtering or aggregations can be performed by SQL Server and the final result set can be small. Limit the result set that arrives to the clients. Any more computations over the data, presentation and formatting are more appropriate on the client side, once the data is received.

Application doesn't fetch results fast enough

If the client application doesn't fetch results fast enough and doesn't notify SQL Server that the result set has been received, the ASYNC_NETWORK_IO wait will occur on the server.

To illustrate using ADO.NET, by default, DataSet and DataTable will fetch all rows to completion before client can access it. However, classes like SqlDataReader allow the application developer to choose what to do after each row is fetched from the server. An application can fetch one row at a time and then process this row according to business requirements. For example:

  • Write the row to a file.

  • Send the row to another application over the network.

  • Wait for some time or for user input.

Resolution: To resolve the issue, fetch all results as fast as the client can by using a tight WHILE/FOR loop. That means storing results in memory and only then doing more processing.

Client application machine is under stress (I/O, memory, or CPU)

Even if application code is developed to fetch results as fast as possible, system resource issues can cause the entire client process to be slow. For example:

The application may not quickly fetch results if the machine that runs the client application has resource constraints. For example:

  • 100% CPU utilization

  • Insufficient memory (all memory is consumed)

  • Slow I/O (perhaps the application writes results or logs)

These resource constraints may lead to slow processing of incoming results and cause SQL Server to experience wait type ASYNC_NETWORK_IO.

Resolution: To resolve this issue, use tools like Performance Monitor to diagnose the system that runs the application, and then eliminate any resource constraints. One of the following methods may work for you:

  • Stop other applications from running.

  • Fix any code issues in those applications.

  • Upgrade the hardware on the system if the applications have been tuned fully.

NIC/Network

Slow network or Network Interface Cards (NIC) can cause delays in network traffic and will naturally delay fetching the results and communicating with SQL Server. Network delays are typically caused by the following issues:

  • Network adapter driver issues

  • Network filter drivers issues

  • Misconfigured or faulty firewalls

  • Routers issues

  • Overloaded networks due to traffic (less common)

Resolution: To diagnose these issues, you can collect a network trace and look for packet resets and retransmits. You can then resolve the network-related issue to eliminate packet resets/retransmits.

See also

ASYNC_NETWORK_IO in sys.dm_os_wait_stats

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors (upstream author metadata: pijocoder). Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 01/17/2025
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。