Over the last few days we have been tracking down the cause of some really high async network IO waits.  In order to fix this problem we have to understand the wait and what it is telling us, so that is where we begin.

Async Network IO in my experience hardly ever means there is a problem with the network, as hinted in the name of the wait.  But most likely a problem with the rate a client is processing data requested from SQL Server is causing you to see high Async Network IO waits.  I have seen once in my career where a server network card was maxed out, upgrading this card helped reduce the wait times.

So lets talk about how to fix the problem on the client side.  So to begin we must understand how SQL presents data to an application.  SQL Serer uses something called the output buffer that “pre-stages” data for clients to grab.  The client grabs the data from the output buffer then processes the data.  Several times I have seen applications process data row by row causing the output buffer to fill because the application is not processing the data as fast as it is requesting the data.

So to resolve this I have recommended in the past that the application review the amount of data that they are requesting (several times they find they do not need to request that much data) to reduce the wait time.  I also have applications review transactions and make sure they are closing transactions frequently and not leaving them open.  And lastly I have recommended applications to use the Top hint in queries to make sure they get a finite amount of results.  These recommendations have helped alot in resolving the async network IO wait but you should also take a second look at the network and make sure you are not maxing anything out (highly unlikely but worth a shot).

The post Async Network IO SQL Wait appeared first on VitaminDBA.