Just a few notes on async_network_io wait. There are several scenarios that I see these commonly pop up:
- Queries returning large datasets
- Client side resource constraints/slow client applications
- MS Access applications
- Network issues (TCP retransmissions, high packet latency)
Queries returning really large datasets can result in this showing up - especially on slower networks.
A client that is 100% cpu constrained or experiencing some other performance degradation can cause this to surface on the database. Be sure to look at if your client calling SQL is having issues - problem might not be in SQL Server, but on the client.
MS Access applications tend to surface this wait event. It's been a while since I've played with Access, but if I remember correctly some of their controls that paginate data initiate a query, but don't consume all the results immediately. Instead it will consume the results as you page through.
Network latency, errors (i.e. retransmits) will cause this to surface. If you query data over a VPN this is another common source of this wait.
I hate to use this to shill our wares, but I think it deserves mention since you are discussing two different approaches above and we tie the two into one tool.
Using sp_whoisactive gives you a current point in time of who is actively running a query. Tools like sentryone and spotlight collect wait stats historically for queries. We combined the two approaches into one. We collect query level wait stats historically, but sample session activity (similar to sp_whoisactive) and store it historically so you can see the sessions tied to the query that experienced async_network_io_wait and see all the details (i.e. program name, user, etc) of the sessions involved on a second by second basis. We have a completely free version of SQLGrease you can use to troubleshoot your problem without being spammed or marketed to.