When a session waits on the “async network io” event, it may be encountering network issues. More likely, however, it may be an issue with the client application not processing the data quickly enough. If the wait times for “async network io” are high.
We can use the below script to know information about the wait type.
,DB_NAME(req.database_id) AS DB_NAME
,req.total_elapsed_time / 1000.0 AS total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
JOIN sys.dm_exec_sessions ses
ON ses.session_id = req.session_id
WHERE req.wait_type IS NOT NULL
–WHERE req.wait_type = ‘?’
If we are getting lots of ASYNC Network IO Then it point to network related issues, but most often are caused by a client application that is not processing results from the SQL Server quickly enough. This will result in filling the network buffers so that SQL Server cannot send more data to the client. Therefore, the process executing the batch will need to wait for the ability to continue sending results to the client.
The “async network io” (in SQL 2005/2008) wait types can Reducing Waits / Wait times by the below method:
- Some applications, such as Microsoft Access, will ask for large result sets (typically identified by select statements with no where clause or full table scans), and then further filter the data on the client. If this is causing significant wait time, see if it’s possible to create a view for the client application to use instead. This will significantly reduce the amount of data being pushed to client since all of the filtering will done on SQL Server. Another fix could be to add a ‘where clause’ or further restrict the query so that less data is being sent to the client.
- Identify large result sets and verify with the application or developer team how the data is being consumed. If the application is querying large result sets but using only a few rows, consider only querying the rows that are needed or use ‘TOP n’ to reduce the number of rows returned.
- If you are encountering high “async network io” wait times when performing data loads on the server, 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.We can use the shared memory connection string to pull the data to minimize the async network io” wait times .
This connection string exemplifies how the network specification can look like inside the connection string.
“Provider=sqloledb;Data Source=126.96.36.199,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User;Password=myPassword;”
Network protocol codes
|dbmslpcn||Shared Memory (local machine connections only, might fail when moving to production…)|
|dbmsrpcn||Multi-Protocol (Windows RPC)|
- Use the ‘TOP n’ clause so that only the rows that the client needs are returned to the client. Investigate network issues, if client application tuning tips do not apply.
If the above tuning tips are reviewed and applied, but the server is still is encountering high “async network io” times, then we need to ensure the below steps there aren’t any network related issues:
• Review counters such as ‘Batch Requests per second’. Any values over 3000 for a 100MB network card are getting excessive. ‘Current Bandwidth’ on the Network Interface with values over 0.6 are getting excessive.
• Check network adapter bandwidth – 1 Gigabit is better than 100 megabits and 100 megabits is better than 10 megabits.
• Look at your NIC configuration on the server to make sure there are no issues with the physical card. Also, check if auto detect is picking the fastest speed.
• Validate all of the network components between the client application and the SQL Server instance (e.g. switches / routers).