MSSQLWIKI

Karthick P.K on SQL Server

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

One Response to “ASYNC_NETWORK_IO or NETWORK_IO”

  1. [...] ASYNC_NETWORK_IO or NETWORK_IO [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,088 other followers

%d bloggers like this: