When your SQL Server’s top wait stat is ASYNC_NETWORK_IO, that indicates SQL Server is waiting on the app to ask for the next part of the result set.
This is a real pain in the rear to troubleshoot because often these waits happen for really short duration each time – think milliseconds, not seconds. It’s an issue of death by a thousand cuts.
Here, I’m running a workload with a lot of short queries running simultaneously. If I just run sp_whoisactive now and then, I don’t see any activity – only a query or two now and then, and they’re rarely waiting on network. However, if I run it several times in a row, right away:
Then I can get a fast, seat-of-the-pants idea of which queries are waiting on networking. I can also scroll across to the right in the result set and see which app it’s coming from, which servers, which logins, etc.
This isn’t an exact science by any means, but when I’m working with a client, it helps me quickly get a rough idea of what’s going on. It helps me start a conversation about the kinds of queries we’re running, how much data we’re bringing back (both in terms of rows and columns), and find out whether the app servers might be underpowered or swapping to disk.
Clients have said things like:
- “Oh, we had no idea that app was bringing back all of the columns, and that’s problematic since we added a few XML/JSON/MAX columns in the table since that query was written.”
- “Oh, we had no idea that application was taking so long to digest the results of those queries. We can give that app server more memory.”
- “Oh, we had no idea that it was bad if we ran the database server in one data center, and the app servers elsewhere.”
ASYNC_NETWORK_IO isn’t necessarily a bottleneck for SQL Server – fixing the above problems may not make a big performance difference overall for other users – so solving it is often a relatively low value during the engagement. sp_whoisactive helps me dedicate the appropriately small amount of time & effort into the problem, and then move on to the bigger problems.