Karthick P.K on SQL Server



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



  2. This piece of writing offers clear idea in favor of the new users of blogging, that
    truly how to do running a blog.

  3. Anil said

    Karthik – Can you please explain how id NOLOCK hint is related to ASYNC_NETWORK_IO wait in SQL Server ?

  4. @sqL_handLe said

    What is the difference between an ASYNC_NETWORK_IO wait with resource_type NULL and one with resource_type ‘External ExternalResource=ASYNC_NETWORK_IO’?

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: