Database

SQL Server ASYNC Network IO Wait Type

November 8, 2012

SQL Server ASYNC Network IO Wait Type

THIS ARTICLE HAS BEEN RECENTLY UPDATED AND CONTAINS NEW, TECHNICAL INFORMATION

Note: For SQL2000 this wait is called NETWORKIO.

The ASYNC_NETWORK_IO wait indicates that one of two scenarios are happening. The first scenario is that the session (i.e., SPID) is waiting for the client application to process the result set and send a signal back to SQL Server that it is ready to process more data. The second is that there may be a network performance issue.

Reducing SQL Server Waits / Wait Times

If there are significant wait times on ASYNC_NETWORK_IO, you have the following options:

  • Review the queries and identify large result sets. Verify that the client application is consuming data as efficiently as possible. For example, if the application is asking for a million rows of data but only processing one row at a time.
  • Review that all rows being requested are necessary. You can often reduce this wait by filtering the result set for only the rows that are needed. Using the TOP clause may be an option as well. Client applications such as Microsoft Access may benefit from querying a view instead of pulling data from an entire table.

If the above tuning tips are reviewed and applied, but the server is still encountering high wait times, then ensure there aren’t any network-related issues:

  • Validate the network components between the application/clients and the SQL Server instance (router, for example).
  • Look at your NIC configuration on the server to make sure there are no issues with the physical card. Also, check if autodetect is picking the fastest speed.
  • Check network adapter bandwidth: 1 Gigabit is better than 100 megabits, and 100 megabits is better than 10 megabits.

 

Also worth mentioning is the common practice of performing data loads on the server. It is possible that you may be seeing the ASYNC_NETWORK_IO wait during the times that the data loads are occurring. If this is the case, then make sure the shared memory protocol is enabled for the SQL Server instance and the session is connected using net_transport = ‘Shared memory’. You can determine the net_transport for the connection by looking at the DMV – sys.dm_exec_connections.

 


Thomas LaRock is a Head Geek at SolarWinds and a Microsoft® Certified Master, Microsoft Data Platform MVP, VMware® vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry as a programmer, developer, analyst, and database administrator. LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010, where his research and experience helped to create the initial versions of the software now known as SolarWinds® Database Performance Analyzer. LaRock has served on the board of directors for the Professional Association for SQL Server® (PASS), and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses on working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle®, MySQL®, SAP®, and DB2®. He’s made it his mission to give IT and data professionals longer weekends.