16

In looking at the list of stored procedures 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 stored procedure 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 stored procedure 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).
Improve this question
6
  • 1
    The best way to think about this type of wait is that there's nothing in your query causing it - it's returning data to the client. You will also see this a lot of you have linked tables in Access. One possibility depending on your client app is to break the data into smaller chunks or just return less data. If that's not an option you probably will be limited in what you can do to reduce it.
    – JNK
    Commented Jan 23, 2014 at 18:35
  • Is the client app connecting using Shared Memory or TCP/IP? Is the client app and SQL Server sharing the same set of processor cores, or are you using an affinity masking technique to separate them?
    – Jon Seigel
    Commented Jan 23, 2014 at 19:19
  • It's the default connection type - nothing special - so it's using Shared Memory. Both apps using the same set of cores - there is no affinity.
    – AngryHacker
    Commented Jan 23, 2014 at 19:38
  • Is your storage SAN or local?
    – Eric Higgins
    Commented Jan 23, 2014 at 20:51
  • @EricHiggins Just a local set of RAIDed hard drives.
    – AngryHacker
    Commented Jan 23, 2014 at 22:44

1 Answer 1

Reset to default
14

As you said, this wait type indicates that the application is not keeping up with SQL Server. Now what that really means is, that SQL Server can't send the data over the network as fast as it would like.

There can be two underlying causes:

  1. The app is written inefficiently and does not process the rows fast enough.
  2. The network is maxed out.

If the application itself is too slow there will be no or no significant impact on the performance of other queries. If on the other hand the pipe is too small, other queries can't send their results either and have to wait.

In the latter case however you would have all connections waiting on ASYNC_NETWORK_IO. You should be able to see that impact clearly.

Improve this answer
2
  • For bullet point 1. The data retrieval is done via ADO.NET using standard code: var dataSet = new DataSet(); var da = new SqlDataAdapter(command); da.Fill(dataSet); So I am not sure what exactly could be slow.
    – AngryHacker
    Commented Jan 24, 2014 at 0:32
  • For bullet point 2. The application is on the same box as SQL and the connection is via Shared Memory method. So theoretically that should be super fast.
    – AngryHacker
    Commented Jan 24, 2014 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.