IO timeout for SQL Server 2005 (ASYNC_NETWORK_IO timeout type) Problem

Question

We are having performance problems in our web application at peak times, which are currently split between one 2003 IIS6 web server and one SQL Server 2005 database server. The query time on the database server looks great (about 30 ms), and the processor is low (less than 20%), however, requests can take a lot of time on the web server (more than 1 second).

I looked in perfmon for tips and saw that the average IO network latency is ~ 400 ms, which completely explains the difference in performance. I also ran some queries in SSMS and see that every second is about 10,000 ms of ASYNC_NETWORK_IO aggregate wait types.

From some studies, it seems that the problem is insufficient network bandwidth or that the web server is not processing the results fast enough. How do I know what it is and then allow it?

We use NHibernate for our data layer, therefore, as I know, DataReaders are not used. There are no known queries that return large sets of results, although there is one table in which there is a column containing compressed XML documents.

Thanks in advance

Additional Information Requested

• Sql server configured to use TCP / IP and shared memory protocols
• We make about 4 database queries per page, nothing funny
• At peak times, the database server sends 1.5 MB / s
• At peak, the web server processor is about 60%
• The maximum AT value, the load of the web server network adapter is 13 MB / s. 8MB sends, 5MB accepts. The same NIC handles SQL and HTTP traffic
• We use some caching, but the application is quite dynamic, so in most cases up-to-date information is required.

Answer 1

We solved the problem by dividing the SQL traffic into another network adapter. In addition, this network adapter connects to the local IP address, while before it hit the public IP address of the database server.

Thanks @Remus Rusanu for the tip.

Answer 2

The database engine expects packets sent to it in the ASP process. This can be either in the ASP.Net process itself (IP packets are removed from the network adapter, but they are not consumed by the process, in which case the ASP.Net host is most likely to be the culprit, both in case of bad code and processor overload) , or it may be that the host operating system cannot consume IP packets fast enough (the combination of loading IP from a combined HTTP load and a load on both ends of SQL is too large for the host).

What is the processor load on the ASP host? What is the NIC interrupt rate? Is HTTP traffic common to one network adapter with TDS (SQL) traffic, and if so, can you split them into another network adapter?

What protocol is configured between the ASP client and SQL? Clean pipes or TCP? If Net Pipes, can you disable it and force it to use TCP so that you deduce SMB from the equation?

How many rounds of a trip to the database do you make for an HTTP request? If you are over 4, can you study code refactoring to reduce it to 3-5 rounds on request?

Are you caching anything in the ASP process to avoid additional database calls?