In looking at the list of sprocs that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the sproc transfers roughly 400 MB of information.
In the documentation, it states that the cause of ASYNC_NETWORK_IO is that the client can't keep up with the flood of data and that is probably true. I am not sure how to make the client keep up since all it does is call the sproc via an ADO.NET and then just processes the dataset.
So given this information, should I worry about the ASYNC_NETWORK_IO wait type for this procedure? Does it actually have an effect on the server performance?
Additional pieces of information:
- I am on service pack 2 of SQL Server 2005.
- The client app is on the same box as SQL Server (I know, i know...but I can't do anything about it).