Sql-server – Reading ASYNC_NETWORK_IO wait stats

sql serverwait-types

I was troubleshooting some bad performance for a client yesterday and identified that there was a missing index for a particular set of queries that were heavily used

(this was for an ERP applicaton: Microsoft Dynamics NAV)

After adding these indexes database performance for the areas that were giving the client grief improved massively, however, during the visit I noticed that at times starting up the application took forever and other users were complaining of freezing and slow response (it didn't happen against all users simultaneously).

The infrastructure consists of a DB server, a service tier X 2 (that runs the application logic and caches data/business object metadata) and the client machine.

I checked the server CPU/memory stats on the service tier and it didn't look taxed at any point (CPU barely got above 50% at any one time and memory was barely touched – around 20-30%)

The SQL server was set to use unlimited memory so that needs addressing but there was actually free memory on the SQL box as all their databases combined don't exceed 10 GB and there is 12 in the server (though the page life expectancy perf counter didn't get much above 200-300 so I'm not sure why this would be)

The users mentioned this had only started happening recently (last 3 weeks) and also mentioned that other applications such as Excel were taking a long time to open files from the network so I had a look at some of the performance counters on the server and ran Brent Ozar's sp_Blitz just to check if there were any things that needed addressing immediately

The script gave me this report on the ASYNC_NETWORK_IO stats:

3437.8 hours of waits, 58.6 minutes average wait time per hour, 100.0% of waits, 0.0% signal wait, 20062055 waiting tasks, 616.9 ms average wait time.

The server has 4 virtual cores

I ran the same script on a server on another client's network (running the same solution) and the script came back with no significant wait stats

I'm suggesting to the client that since they don't need the 16 GB allocated to the service tiers (as they barely get near 4-6 GB usage) that they should consider reallocating some of the memory to SQL as their DB will grow (NAV databases tend to grow quickly), but I want to advise them best on if there could be a network issue

How do I correctly interpret these waits? Am I looking at a potential network issue as my gut says yes?

Best Answer

While the ASYNC_NETWORK_IO wait type is usually a consequence of the application design, the network issues can also cause the stockpiling of the ASYNC_NETWORK_IO wait type.

It is advised to check the following, regarding the network issues:

  • Check network bandwidth between the SQL Server and client
  • Make sure that all network components between the SQL Server instance and the client, such as routers, switches, cables are properly configured, fully functional and dimensioned according to required bandwidth
  • Review the Batch requests per second counter values, as this could often indicate the reason for high ASYNC_NETWORK_IO waits
  • Make sure that Auto Negotiate of the NIC is detecting the network bandwidth properly

Also -checking the NIC bandwidth utilization is prudent, even often overlooked.

Please, take a look at the Reducing SQL Server ASYNC_NETWORK_IO wait type aticle, to get more details on interpreting high ASYNC_NETWORK_IO wait type values.

Related Question