In continuation to my previous posts on waits, [Waiting on wait tasks, adds more weight to fix a performance problem] I got few follow up references on the other wait types async_network_io (SQL 2005 onwards) and networkio (until SQL 2000).
Just on basics both the wait types relates to the network, which means the data transfer or buffer sent to client or application. During this transition the network plays an important role where the acknowledgement is key part to get the process completed. When you see high numbers (more than 1000) then its a bottleneck on network for that program or process on SQL Server. This will lead to a blocking of queries and causing other queries duration increases.
In order to reduce the impact the generic guidance is to limit number of rows that are fetched from SQL Server, ok its easy when you find that a particular set of queries are causing such a problem. However if the SQL Server is joined with IIS or any other third-party application then its a definitive problem.
There are few workaround options you can try:
- Ensure that the client application processes are designed/coded that all the data requested is handled efficiently.
- If those processes are using any query based technique then you might use TOP to return certain number of rows at a time. N
- Always best to validate the network components on SQL Server and Application server, also do not forget to have a diagnostics run on router/switches/hubs within the network.
- Perform a diagnostics on NIC card.
- Also disable Shared Memory protocol on the SQL Server instance. To find out whether it is enabled: select net_transport from sys.dm_exec_connections
Finally to see the ASYNC_NETWORK_IO wait type values run the following TSQL:
SELECT HOST_NAME, PROGRAM_NAME, wait_resource, wait_time, wait_type,DMER.session_id,start_time,DMES.status,plan_handle,auth_scheme,local_tcp_port,local_net_address
FROM sys.dm_exec_requests DMER
INNER JOIN sys.dm_exec_sessions DMES ON DMES.session_id=DMER.session_id
INNER JOIN sys.dm_exec_connections DMEC ON DMEC.session_id=DMES.session_id
Also few relevant posts on wait-types:
Republished from SQLServer-QA.net [10 clicks].
Read the original version here [3 clicks].