ASYNC_NETWORK_IO, as the name suggests it is a wait type associated with the network. Infact it deals with the network buffers.
Does it mean it is completely a network issue? - Never, it might be a network issue or not. However we need to check our end to investigate and solve the underlying problem before proceeding with hardware issues. I still rememebr a graph from MSDN describes the where the perfromance design and tuning efforts are best focused.http://msdn.microsoft.com/en-us/library/ff647793.aspx
Okei, hope you would have looked at the MSDN graph in the above link. As MSDN suggests we should always start looking at the application side and then Database before we jump into Hardware to get the benefit of maximum performance improvement.
To explain better our case, I would like to take an example of real time scenario as a queue.There would be only one person serving the queue and there would be many people requesting for.The movement of queue always depends on the serving person. if the person or system is not efficient to process the requests, the queue has to wait more time.
In the same way,for any data transfer between database and application, it has to pass through the network in real time scenario.Hope you have database and application configured in two different servers :) If the client side is not efficient to handle the request, the database has to wait more time. In short, here it is not the issue with the network rather the client.
Then why does it show a Network issue(ASYNC_NETWORK_IO)?
- Yes it will show as Network issue. As we discussed, when data passes to client it passes through network. If Database is sending the data faster than the client that process, netwrok buffer will get fill resulting the database will not be able to send more data to the client. In SQL server, it results with a ASYNC_NETWORK_IO wait type.
One of my server, I have faced such issue where around 272771 records were sending from database to application, but the client was processing the data row by row resulting wait type as ASYNC_NETWORK_IO and huge total elapsed time.(Figure is as below:)
To resolve the issue, we have modified the client code a bit to recieve and process the data as a batch.(I really wanted to preesnt the change/snippet we made here, but my current contract never allows this. My apologies.) The result of the proof of concept was huge reducing the total elapsed time from 2 hrs to 10 sec. Take away, if you see such wait types, please evaluate your client code and make sure there are no major bottlenecks at client side before jump into network.Once you make sure it, the next step we can look at our network configurations.
Republished from SQL - My Best Friend [58 clicks].
Read the original version here [32134 clicks].