** 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).