What are the top wait types that you come across on your SQL Servers? Is ASYNC_NETWORK_IO one of them? Let’s go over why you might be seeing ASYNC_NETWORK_IO waits and how to get them cleaned up.
Slow Application Processing
ASYNC_NETWORK_IO waits can occur when your application isn’t processing your SQL Server data fast enough and SQL Server is stuck waiting to get the signal that it’s ok to process more data. Are you familiar with the acronym RBAR? RBAR stands for “Row By Agonizing Row” and is a scenario where data is retrieved one row at a time. This is a very slow way to work. If you want a spoonful of sprinkles on your ice cream, do you pick out and place each sprinkle one by one? No, because your ice cream would be melted by the time you got all of your sprinkles. You take a spoonful (or set) of sprinkles and move on. It’s better to grab all the data you can at once to cache and then work with it.
Slow Network
If you are satisfied with how your application is handling data (surely there are no unnecessary SELECT * queries, right?) yet you are still seeing ASYNC_NETWORK_IO waits, you may in fact have a network problem. From the SQL Server, try pinging the client web server to find the ping time and compare that with the average wait time. If the ping time you gathered is close to the average wait time, you probably found your root cause of the ASYNC_NETWORK_IO waits. Time to track down NIC settings, router settings, etc. to see if there’s a configuration change that can be made to improve the situation.
Checking for ASYNC_NETWORK_IO Waits
If you’re trying to track down what server is causing the wait, setup an Extended Events session and watch for the wait type to show up. If it’s happening more frequently, it might be easier to catch the query or queries with sp_WhoIsActive. Once you have the query, you can test to see if you can run that same query in a reasonable time in SSMS when configuring SSMS with no results to grid. Since you’re not as worried about readability and more importantly don’t want to wait on added time for the results grid to be populated, try results to text instead and test how the query performs.
When it comes to researching ASYNC_NETWORK_IO waits or any other wait, check out https://www.sqlskills.com/help/waits/. As of the time of this blog post, they cover 1236 different wait types with detailed information on 356. That’s a lot of waits. Chances are that this site will have some info to help you out.
Thanks for reading!
Chad Callihan 