WWSD – Weekly Wait Stats Definitions
I decided that it might be a good idea to put together a list of the most common Wait Stat types with a quick definition and some possible ideas on how to resolve them. Most all of us have been there before, we encounter some slowness in our queries and run our favorite DMV scripts to see what is causing it, only to find out that there is a list of crazy looking culprits. What are all of these and how do we fix them? Well, hopefully these posts will help to resolve some of those questions.
I will start off WWSD with ASYNC_Network_ IO.
What is ASYNC_NETWORK_IO?
This Wait Stat is typically not an issue caused by SQL server but rather by the client/application or the network itself. What ever it is that is consuming the data might not be efficient at it or perhaps the network simply can not handle the load that SQL is trying to send through it. When this wait occurs, it means that SQL is waiting for the green light to pass more information. It is like being in a traffic jam.
Things to look at?
Perhaps the client/application is processing the data received from SQL one row at a time which is causing a back-log. If the data is being filtered or sorted on the client, then perhaps the query retrieving the data could be tuned to filter on the database side instead. Engage your network and server teams (and be nice) to ensure the hardware running the application and the network between is capable of handling the load. Things to look at are network card properties, switches, routers and bandwidth. Based on what I have read around the web, a 100Mbs network card can only handle about 3000 Batch request per second. Other things to think about is VPN connections and Wide Area Networks (WAN’s).