减少SQL Server ASYNC_NETWORK_IO等待类型

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.

ASYNC_NETWORK_IO等待类型是DBA经常看到的那些等待类型之一，当出现过多的值时，它可能会令人担忧，因为它是最难解决的等待类型之一。

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

重要的是要知道从SQL Server 2005开始采用ASYNC_NETWORK_IO名称，而在SQL Server 2000中，此等待类型称为NETWORKIO。 此等待类型的原始名称起源于2000年代中期之前通常使用的10兆位和100兆位的慢速以太网速度。

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.

在大多数情况下，这种等待类型的值实际上与任何网络问题都不相关（或者是非常罕见的情况），尤其是在当今非常快速的以太网速度（40 Gigabit或100 Gigabit，以及200 Gigabit和400 Gigabit速度）下目前正在开发中。

Excessive ASYNC_NETWORK_IO waits could occur under two scenarios:

在两种情况下，可能会发生过多的ASYNC_NETWORK_IO等待：

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

会话必须等待客户端应用程序处理从SQL Server接收的数据，才能向SQL Server发送信号，表明它可以接受新数据进行处理。 这是常见的情况，可能反映了错误的应用程序设计，并且最常见的原因是过多的ASYNC_NETWORK_IO等待类型值

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

过多SQL Server ASYNC_NETWORK_IO等待类型的最常见原因是应用程序无法足够快地处理从SQL Server到达的数据。 当应用程序请求大数据结果集时，缓慢的数据处理将导致数据缓冲区被填充，从而阻止SQL Server向客户端发送新数据。 逐行逐行（RBAR）处理通常是这种行为和较高的ASYNC_NETWORK_IO等待类型值的原因。 在RBAR应用程序编程中，一次仅从SQL Server发送的结果集中处理一行。 在这种情况下，将缓存可处理的完整结果集，然后通知SQL Server该数据集已被“处理​​”。 这将允许SQL Server在应用程序处理缓存结果集中的数据时发送新数据集

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.

当使用RBAR处理的应用程序被迫与非常大的数据库环境（VLDB）一起使用时，它通常会在处理数据时遇到问题。 执行批处理的服务器进程（SPID）将被迫等待，直到应用程序设法开始处理存储在缓冲区中的数据为止，从而允许SQL Server通过缓冲区将新结果集发送到客户端。 在等待将应用程序请求的新数据发送到缓冲区以进行进一步处理时，它会生成ASYNC_NETWORK_IO等待类型。

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

那么，当DBA在SQL Server上遇到较高的ASYNC_NETWORK_IO等待类型值时，该怎么办？ 这涉及调查导致过多ASYNC_NETWORK_IO等待类型值的应用程序，并经常与创建它的应用程序开发人员进行协调。 在调查过多的ASYNC_NETWORK_IO等待类型值时，应检查以下内容

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

   检查应用程序是否正在从SQL Server实例请求大数据集，然后检查它是否在客户端筛选这些数据。 请注意第三方应用程序，例如Microsoft Access或ORM软件（也称为对象关系映射 ），它们可能正在请求在客户端过滤的大型数据集。 使用立即读取和后续处理编程方法通常可以使用户避免过多的ASYNC_NETWORK_IO等待类型值

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

   确保为客户端应用程序创建了适当的视图，因为这可以确保由SQL Server实例完成数据筛选，因此将大量数据发送给客户端应用程序

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

   检查是否有一种方法可以减少请求的数据集，从而可以直接在SQL Server上执行数据筛选

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

   如果是单个查询或ad-hock查询，请确保在可能的地方添加WHERE子句，并且已对查询进行了适当的优化，以将请求的数据集限制为仅所需的数据

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

   检查是否可以在查询中使用“ TOP n”来减少查询将返回的行号

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

   标量值用户定义函数（UDF）通常是由于RBAR而导致ASYNC_NETWORK_IO等待类型过多的原因，因此请查找这些对象的任何实例，这些实例可能会影响性能。

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

   将具有用户定义函数（UDF）定义的计算列与大型数据库一起使用是由于RBAR而导致ASYNC_NETWORK_IO等待类型较高的另一个常见原因

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

   对于SQL Server 2016，可以使用在大多数情况下可以显着降低RBAR的本机编译UDF，并将执行速度提高到100％。 这在无法将UDF重构为表值函数的情况下特别有用

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

注意：
SQL Server Management Studio是一个臭名昭著的客户端应用程序，以其生成ASYNC_NETWORK_IO等待类型的声誉而闻名。 SSMS一次读取一行数据流，并在检索下一行之前处理每一行

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:

另外，当需要处理过多的ASYNC_NETWORK_IO等待值的情况时，甚至在SQL Server端处理大量数据时，也可以通过直接调整SQL Server来完成其他一些事情：

• 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:

  如果尚未为该SQL Server实例启用共享内存协议
  使用以下查询来确定当前连接所使用的协议：

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

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

  确保使用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

如果检查了以上所有内容，并且SQL Server仍然受到较高的ASYNC_NETWORK_IO等待值的攻击，那么现在该是检查可能引起此类行为的与网络相关的潜在问题的时候了。 通常有不同的原因是由于物理网络限制，故障或由于错误的网络设置而导致的简单原因。 为了对由ASYNC_NETWORK_IO等待引起的网络进行故障排除，应仔细检查以下内容

网络问题 (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

  检查SQL Server和客户端之间的网络带宽。 带宽不符合客户端应处理的估计数据量的带宽的慢速网络适配器是ASYNC_NETWORK_IO等待值较高的常见原因。 仍然存在100兆位适配器，它们通常无法满足现代SQL Server数据库的需求和处理的数据量。 即使在许多环境中，即使切换到1 Gigabit适配器，仍会使系统低于当前要求。 在大多数环境中，使用10 Gigabit网络适配器是一种最低要求，而在不久的将来，许多企业将不得不转换200 Gigabit和400 Gigabit，如果他们还没有这样做的话

• 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

  确保在SQL Server实例和客户端之间的所有网络组件（如路由器，交换机，电缆）均已正确配置，功能齐全且根据所需带宽确定了尺寸

• 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

  查看“每秒批处理请求”计数器的值，因为这通常可以表明ASYNC_NETWORK_IO等待时间过长的原因。 检查批处理请求时，必须检查的是SQL Server处理的T-SQL批处理的数量，因为这将确定SQL Server每秒处理的批处理数量。 每秒“批处理请求”值大于1000的服务器被视为“忙”。 推荐值可能在很大程度上取决于实际的系统配置，活动级别以及正在处理的事务数量。 在高峰时段，该值可能会更高，这并不罕见

  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

  当在100兆位网络上遇到“每秒批处理请求”计数器值接近或大于3,000时，这几乎可以肯定地表明网络速度是瓶颈，并导致高ASYNC_NETWORK_IO等待值。 如今，随着服务器每秒轻松处理超过20,000个批处理请求，考虑将1 G或更低的网络升级到10 G来满足对SQL Server数据处理不断增长的需求是明智的。

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

  谨慎检查NIC带宽利用率，甚至经常被忽略。

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

  使用Perfmon，可以通过以下公式轻松计算网络利用率：
  网络利用率％=（（总字节数\秒* 8）/当前带宽）* 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

  如果值通常大于60％，则强烈建议切换到更快的网络适配器/网络带宽，以确保在需要进行数据处理时可以分配足够的带宽。

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

  确保NIC的自动协商正确检测到网络带宽

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

  要检查所有活动网络连接的当前速度，请使用以下CLI命令

  wmic NIC where NetEnabled=true get Name, Speed

  Wmic NIC，其中NetEnabled = true获取名称，速度

  

  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

  如果针对特定适配器的自动协商未选择正确的网络速度，则可以在NIS属性中手动设置NIC速度

  

翻译自: https://www.sqlshack.com/reducing-sql-server-async_network_io-wait-type/