ASYNC_NETWORK_IO or NETWORK_IO

What is ASYNC_NETWORK_IO or NETWORK_IO ?

When a query is fired, SQL Server produces the the results ,place it in output buffer and send it to client/Application.
Client/Application then fetch the result from the Output buffer, process data  and send acknowledgement to SQL Server. If client/Application takes long time to send acknowledgement then SQL Server waits on ASYNC_NETWORK_IO (SQL 2005/2008) or  Network_IO (SQL 2000) before it produces additional results.

Impact of this wait type

1. SQL Server will not release the locks held by the query unless Acknowledgement is received from Client. Which might cause blocking.

2. Query duration increases so the query will be slow.

How to troubleshoot ASYNC_NETWORK_IO or NETWORK_IO wait type?

Limit the result set: Limit the number of rows you fetch from SQL Server. Many application designers fetch the data from whole table with out filtering (Where clause) and do the filtering on client side. This is very wrong approach  there has to be filtering on server side.
If there is need to fetch huge number of rows from server and if other sessions are experiencing blocking because of ASYNC_NETWORK_IO  or  NETWORK_IO  Then  insert all the rows in to temp table and do the select from temp table.   

Check the performance of client application:   Check if client / Application system is experiencing system resource bottleneck. Application will not be able to process the result set faster if there system resource bottleneck

Network: Check the network speed between SQL Server and  Client/Application system. If the network is slow application can not fetch result faster.

No lock hint or Snapshot isolation level: Check if you can use No lock hint or Snapshot isolation level

 

 

 

Few other SQL Server performance blogs :

Basics of SQL Server Memory Architecture

SQL Server I/O Bottlenecks

Troubleshooting SQL Server Memory

SQL Agent MaxWorkerThreads and Agent subsystem

 

 

Thank you,

Karthick P.K |Technical Lead | Microsoft SQL Server Support |

My Facebook Page |My Site| Blog space| Twitter