We have a SQL Server 2005 (64-bit Std) on Win2k3 R2. We're seeing a disproportionate number of ASYNC_NETWORK_IO waits that (seem to) result in unavailability of the database, which slows or hangs our customer-facing (dynamically generated, database-dependent) website.
We're reasonably certain these waits are caused by slow/non-optimized back-end apps — MS Access forms and ASP pages that make lots of requests for lots of data but don't consume the results quickly enough. Obviously it would make sense to optimize or replace the offending apps, and/or decouple them so they aren't constantly and inefficiently hammering the website's data source. We're doing those things, but it's very time consuming.
In the meantime I'm looking for a temporary/short-term palliative, and I ran across this helpful article, from which I gather that ASYNC_NETWORK_IO waits are really a network buffer problem: if the clients aren't consuming data quickly enough, the SQL Server's network buffers fill up and the server becomes unavailable.
So: Can we simply increase the network buffer size, so even if the naughty apps are running slowly, the SQL Server remains available to the website?
And if yes, how do we do that? For the life of me I can't find any resources that say how to increase network buffer size. I don't know whether this is a NIC issue, a Windows issue, or a SQL Server-specific issue. (We're not short on RAM or disk space, and are fine with throwing hardware/money at the problem.)
If no, then any other suggestions for the short-term, while we're busy rewriting all the old code, would be greatly appreciated.
Thanks in advance … !
Best Answer
Yes, async_network_IO waits are often a symptom of an application taking very large chunks of data whilst it only really needs a little. The problem over the network is the back and forth nature of the transmission.
You mention ASP - if this is classic ASP, this often had the development problem of looping through a recordset - which was slow. The correct approach was to use getrows, which took the data in one gulp onto the app (web) server rather than back and forth.