ASYNC_NETWORK_IO Qyery running slow
I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?
Environment
SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.
Thanks
-Ashis
Do you really need to return 800K rows back to the client. That is a lot of data to push through. Also, all the data must be loaded into memory on the client. This will definitely be a problem because most client desktops are not that powerful.
Anyway, consider updating your sqlserver indexes and make sure all statistics are up to date. If you are trying to do paging, here is a good start.
http://www.aspfaq.com/show.asp?id=2120
oj at 2007-9-3 >
ASYNC_NETWORK_IO means that SQL Server is waiting on the network. Returning 800000 rows to the client is not a good idea. If you must do it because a concrete business requirement, you should make sure the client(s) have gigabit ethernet cards, your network is using switches instead of hubs, etc. You should also see if you can reduce the number of columns returned to reduce the network load a little bit.
But really, returning 800000 rows is the core of the problem. Finally, a SP that takes 13 seconds to return 800K rows from a single table running in SSMS on the server tells me that you probably have index problems, statistics problems or completely underpowered hardware. You might want to give us the table schema, the SP and the output from sp_HelpIndex for the table, and the IO statistics output when you run the query with SET STATISTICS IO ON
I have the same type of problem with ASYNC_NETWORK_IO and SSIS packages. Seems like if the SSIS package is having to deal with a lot of data or with a lot of computing, it starts out good and gets slower and slower and slower. It seems to affect the whole box.
Does anyone know of any memory leaks or issues with SSIS and large data sets (like millions of rows)? We had a tech guy from Microsoft tell us that SSIS is limited to 2 gig of memory on a 32 bit platform. Which does not make sense to me.
Oh, I am running on a 4 CPU dual core box with SQL 2005 sp1 and 16 gig of memory. The box is in the datacenter and has gigabit connection. The storage is an HP XP San with fiber connections. Sounds impressive but I am not a network guy so I just take their word.
Any help would be greatly appericated!!!
Jim Youmans
jdyoumans at gmail dot com
