Troubleshooting ASYNC_NETWORK_IO, NETWORKIO


** cross-posted from www.joesack.com as I reference this one quite a bit **



As you may already be aware, the ASYNC_NETWORK_IO (seen in SQL 2005) and NETWORKIO (seen in SQL 2000) wait types are associated with either a calling application that is not processing results quickly enough from SQL Server or is associated with a network performance issue.


As I received a question on this today, I thought I would share prescriptive guidance on what to do if you see ASYNC_NETWORK_IO or NETWORKIO wait types:


– Identify large result sets and verify with the application team (or developers) how this is being consumed.   Red flags include the application querying large results sets but not processing more than a few rows at a time


– Ensure that the client application processes all rows it is requesting.   If not all rows will be needed or used – consider only querying the rows that are needed (TOP X for example)


– Look at your NIC configuration on the server and make sure there are no issues (physical card issue, autodetect not picking the fastest speed)


– Validate the network components between the application/clients and the SQL Server instance (router, for example)


As a side note – if you are doing server-side data loads that are still showing NETWORKIO, check to see if shared memory protocol is enabled for the SQL Server instance (and then check to see if session is connected using net_transport = ‘Shared memory” via sys.dm_exec_connections).  


Comments (6)

  1. Jimmy May says:

    Excellent post, Joe.  Too few folks don’t even use wait stats.  Those who do see one or both of these waits high on the list & think they have network problems.

    In fact, as you point out the problem is often the client not consuming information from SQL Server sufficiently quickly.

    For example, my current client’s appliation  is sending across the wire 1) millions of rows without batching and 2) large blobs.  Guess what waits sort to the top?

  2. Thanks for the info, I've had a NETWORKIO wait before whilst performing a server-side operation and it was driving me crazy trying to figure it out.  I'm pretty sure I have shared memory enabled but next time it happens I'll check sys.dm_exec_connections as you suggested.  

  3. Thanks for the info, I've had a NETWORKIO wait before whilst performing a server-side operation and it was driving me crazy trying to figure it out.  I'm pretty sure I have shared memory enabled but next time it happens I'll check sys.dm_exec_connections as you suggested.  

  4. josephsa says:

    Glad the post helped, Ashley.  Thanks for the comment!

  5. Andy Ansryan says:

    I noticed this issue when I upgraded our prod to SQL 2008 on win 2008R2 while the web servers are on IIS 6 and wind 2003. We are going to upgrade the web server to IIS7.0 on win 2008R2. Hopefully with the new tcp/ip stack in Win 2008R this bottleneck will be resolved.

  6. josephsa says:

    Thanks Andy.  If you remember to do so – please post back on here to let us know if that upgrade does indeed do the trick.