ASYNC_NETWORK_IO or NETWORK_IO

ASYNC_NETWORK_IO or NETWORK_IO

Translated from: https://mssqlwiki.com/sqlwiki/sql-performance/async_network_io-or-network_io/

What is ASYNC_NETWORK_IO or NETWORK_IO?

When the query is executed, SQL Server processes the result, puts it in the output cache, and sends it to the client or application.

The client or application then gets the results from the output cache, processes the data, and sends an acknowledgement to the SQL Server. If the client or application takes a long time to send an acknowledgement, SQL Server waits for ASYNC_NETWORK_IO (SQL Server 2005Accord2008) or NETWORK_IO (SQL Server 2000) before processing the additional results.

The impact of this wait type:

1.SQL Server will not release the lock held by the query unless the client receives a confirmation message. This can lead to blockage.

two。 The query duration increases, so the query is slow.

How do I troubleshoot ASYNC_NETWORK_IO or NETWORK_IO wait types?

Restrict the result set: limit the number of rows fetched from the SQL Server. Many application designers get data from the entire table without using Where clause filtering and filter it on the client side. This is a very wrong way and should be filtered on the server side.

If you need to get a large number of rows from the server, and if other sessions are blocking due to ASYNC_NETWORK_IO or NETWORK_IO, insert all rows into the temporary table and query from the temporary table.

Check the performance of the client application: check to see if the client or application system is experiencing a system resource bottleneck. If there is a system resource bottleneck, the application will not be able to process the result set faster.

Network: check the network speed between the SQL Server and the client or application system. If the network is slow, the application cannot get the results faster.

NOLOCK prompt or snapshot isolation level: check to see if you can use the NOLOCK prompt or snapshot isolation level.