0

I work on a client side application developed in c# which has a SQL Server back end.

On the server side we are noticing a decrease in performance as the number of users and the amount of data starts to grow.

We have identified a number of design problems, which have been resolved and have helped improve performance. During our review we also noticed the following. In SQL Server, ASYNC_NETWORK_IO was continuously appearing high in our top 10 wait statistics.

To the best of my knowledge, this can be the result of Row by Row processing on the client side. To verify this, my questions are as follows:

  1. Am I correct in saying, if SQL Server can return a results set quicker than SqlDataReader can read it due to RowByRowPro, then the data will be stored in the Network Buffer. If this is full, SQL Server has to wait for space to become available; hence the ASYNC_NETWORK_IO wait stat grows. Is this correct?

  2. If my statement in question 1 is correct, how much of an overhead can this apply to SQL Server when dealing with other requests outside of the one that is currently waiting?

  3. Lastly, has anyone created a test case that could show the effect of the above?

Improve this question

1 Answer 1

Reset to default
2
  1. SQL Server doesn't consider a query done until the client confirms it. Slownes in client-side processing is the most frequent cause for large ASYNC_NETWORK_IO (network issues being the second one).
  2. The consequence is that the resources are not freed in time - memory, scheduler..
  3. Not a test case, but had a real client consuming couple of GBs worth of data, reading it row by row and manipulating it row by row. It wasn't my priority at the time to scientifically measure the impact, but I recall it slowed down the server considerably, so I killed the client (process, not the actual guy).
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.