Reading ASYNC_NETWORK_IO wait stats

Question

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?

Answer 1

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.

Answer 2

As a database guy I had a similar discussion with our NAV team about ASYNC_NETWORK_IO as their users were complaining about bad performance.

Short answer: This is a normal thing to happen for NAV 2013 - and nothing to really worry about.

If you search for this wait type and NAV you find some posts like this (last paragraph): https://blogs.msdn.microsoft.com/nav/2014/11/12/microsoft-dynamics-nav-faster-than-ever/ .

My interpretation of this article and my observations: The queries NAV sends do not include a TOP 100 or similar all the time. So if the result set is 1000 rows NAV will fetch and process the first 100 rows - and leave the instance hanging until the transaction or connection closes. The worker process will fill it's send buffer and pause - which shows as ASYNC_NETWORK_IO ...

Answer 3

as Paul Randal explains here and here the main reason for async_network_IO is RBAR.

ASYNC_NETWORK_IO This is usually where SQL Server is waiting for a client to finish consuming data. It could be that the client has asked for a very large amount of data or just that it’s consuming it reeeeeally slowly because of poor programming – I rarely see this being a network issue. Clients often process one row at a time – called RBAR or Row-By-Agonizing-Row – instead of caching the data on the client and acknowledging to SQL Server immediately.

and the condition you explained seems a case of it.