I have an ASP.NET web application that hangs on a single database UPDATE command for 5+ minutes. I can see this occur in SQL Profiler. This is a one row UPDATE statement on a small table (~600 rows). There are no JOINs or sub queries. There are no other users using the system. During this 5+ minutes, I can see the job in Enterprise Manager with a wait type of NETWORKIO. Since both IIS And SQL Server are running on the same system, the network shouldn't be an issue. Any ideas?
Typically, a waittype of NETWORKIO means that SQL Server is waiting on the front end application to do something. I have seen this much more often with programs that loop through a record set. The select statement that generates the recordset must wait for the program to say "OK, I have all the data, now. You can close out." I am not sure how this would happen with an update statement, though.
First, Note These symptoms are the same as the symptoms that are seen when a client application does not fully fetch query results. Incomplete result set processing is a much more common cause of the symptoms. In other words, this hotfix won't fix poorly designed applications.
Next, the total size (width x height or rowsize x rows) is what matters.