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
Troubleshooting SQL Server Memory
SQL Agent MaxWorkerThreads and Agent subsystem
Thank you,
Karthick P.K |Technical Lead | Microsoft SQL Server Support |