Sql-server – Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections

We actually ran into a similar issue at StackOverflow and Kyle blogged about it:

http://blog.serverfault.com/2011/03/16/views-of-the-same-problem-network-admin-dba-and-developer/

The problem can be a number of things:

• Queries bringing back too much data (like select * from a wide table with a lot of XML or binary fields)
• Client-side apps processing data row by row instead of pulling it all into memory and then doing whatever work necessary
• Underpowered app server hardware (or in your case, client machines) that are paging to disk or churning on CPU. I see this a lot on over-committed virtual machines.

To tune it, start by looking at Perfmon counters on your own machine. My tutorial on it is at http://www.BrentOzar.com/go/perfmon, and it includes a list of counters to gather, how to analyze 'em, and how to interpret your bottleneck.

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.