SQL Server 2005 Network IO Wait Times (ASYNC_NETWORK_IO Wait Type) Problem

Question

Hi,

We are having performance issues on our web app during peak times, which is currently split between one 2003 IIS6 Web Server, and one SQL Server 2005 DB Server. The query times on the DB server look fine (around 30ms), and the CPU is low (under 20%), yet queries can take a long time to execute on the web server (over 1 second).

I was looking in perfmon for clues, and saw that the Average Network IO wait time is ~400ms, which could well explain the performance difference. I also ran some queries inside SSMS and see that every second there are roughly 10,000ms of cumulative ASYNC_NETWORK_IO Wait Types.

From some research, it seems that the problem is either insufficient Network capacity, or that the web server isn't processing the results fast enough. How do I go about investigating which it is, and then resolving?

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

Thanks in advance

Additional Requested Information

• Sql Server is set up to use TCP/IP and Shared Memory protocols
• We make around 4 DB requests per page, nothing ridiculous
• At peak time DB Server sends 1.5MB/sec
• At peak, Web server CPU is around 60%
• AT peak, Web server NIC load is 13MB/sec. 8MB is sending, 5MB is receiving. The same NIC handles SQL and HTTP traffic
• We do use some caching but the application is pretty dynamic so requires up to date data most of the time

Answer 1

The database engine is waiting on the ASP process to consume the packets sent to it. This can be either in the ASP.Net process itself (the IP packets are dequeued from the NIC, but they are not consumed by the process, in which case the ASP.Net host is likely the culprit, as in either bad code or CPU overload), or it can be that the host OS cannot consume the IP packets fast enough (the combination of IP load from the combined HTTP load and the SQL round-trip load is too much for the host).

What is you CPU load on the ASP host? What is the NIC interrupt rate? Is the HTTP traffic shared on the same NIC with the TDS (SQL) traffic, and if yes, can you separate them on a different NIC?

What protocol is configured between ASP client and SQL? Net pipes or TCP? If is Net Pipes, can you switch it off and force it to TCP so you take the SMB out of the equation?

How many round trips to the database do you do per HTTP request? If is more than 4, can you look into refactoring the code to reduce it to 3-5 round trips per request?

Do you cache anything on the ASP process to avoid extra round-trips to the database?

Answer 2

We resolved the problem by separating the SQL traffic on to a different NIC. In addition, this NIC connects to a local IP Address, whereas before it was hitting the public IP of the database server.

Thanks to @Remus Rusanu for the tip.