July 14, 2011 at 5:50 am
Hi all,
We are getting a lot of large ASYNC_NETWORK_IO waits. From what i gather this is either the app reading the data its requested from SQL or the network taking a long time to get the data from SQL to the app.
Does anyone know a good way to prove which is happening. Perfmon counters? some fancy DMV?
Background
This is a 3rd party app. whos support has been suspect at best loving to pass back the blame on netwrk, slow servers, poor storage etc before looking at there own app (even when we have proof its not any of those things) so getting as much info as possible to prove this a best bet.
The SQL Server is a 2008 r2 2 node cluster connecting to a SAN via FC (No idea if this matters but trying to give as full a detail as possible)
Im seeing the waits on the free version of Confios Ignite. So it doesnt record it for longer than 24 hours. Every time there the WAITS goes to full all the queries are waiting on the ASYNC_NETWORK_IO.
any help or suggestions most welcome!
S
July 14, 2011 at 7:37 am
Here's the best data I can find on the subject: http://www.confio.com/English/Tips/Async_Network_IO.php
It's one of those things where you have to dig into the application a bit to find out exactly what's going on. No way to do it from the server that I know of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 2:30 am
Sorry for the delay in replying
Thanks for the info!
I know the front end is a webpage. But i believe all the queries go though another server before hitting the server or in reverse hitting the client. so maybe the delay is there. There trying to get about 500,000 rows of data sometimes more so i guess a wait is expected but these waits go up from 2-10 mins :X
I will see what else i can find out and go from there.
thanks again for the help.
S
July 20, 2011 at 7:20 am
Typically this wait indicates that the calling app is not consuming the rows as quickly as SQL Server wants to spool them out. Poorly written app, network bottleneck, overloaded client/web/middle tier machine, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 20, 2011 at 8:47 am
Very interesting. I just found this thread today and I was dealing with this exact issue for the first time yesterday. We have a SQL2008 Active/Active cluster and a new app that went live this week. This is the first time they have done a specific process on the app and get ASYNC_NETWORK_IO waits. If I run the query on my PC or on the SQL Server it runs in 1 second and returns about 5,000 rows. Sometimes they do this on the app server and it just hangs and one got stuck in that state for over 14 hours before I killed it. I found an article that talked about this issue and some things to check. All SQL Statements have been examined and I cannot tune them any more than they are already. See this below:
ASYNC Network IO
The “async network io” (in SQL 2005/2008) and “networkio” (in SQL 2000) wait types can point to network related issues, but most often are caused by a client application that is not processing results from the SQL Server quickly enough. This will result in filling the network buffers so that SQL Server cannot send more data to the client. Therefore, the process executing the batch will need to wait for the ability to continue sending results to the client.
Reducing Waits / Wait times:
If there are significant wait times on “async network io’, review the client applications. Most often, client applications will process rows one at a time using fetches. This may cause the server process to wait on “async network io” when serving up many rows. If this is the issue, there is nothing you can do to improve the SQL Server process performance. Instead, the client application (or middle tier if a web application) may need to be modified to allow for more efficient retrieval of data. Review the following list for client application issues:
•Some applications, such as Microsoft Access, will ask for large result sets (typically identified by select statements with no where clause or full table scans), and then further filter the data on the client. If this is causing significant wait time, see if it’s possible to create a view for the client application to use instead. This will significantly reduce the amount of data being pushed to client since all of the filtering will done on SQL Server. Another fix could be to add a ‘where clause’ or further restrict the query so that less data is being sent to the client.
•Identify large result sets and verify with the application or developer team how the data is being consumed. If the application is querying large result sets but using only a few rows, consider only querying the rows that are needed or use ‘TOP n’ to reduce the number of rows returned.
•If you are encountering high “async network io” wait times when performing data loads on the server, make sure the shared memory protocol is enabled for the SQL Server instance and the session is connected using net_transport = ‘Shared memory’. You can determine the net_transport for the connection by looking at the DMV – sys.dm_exec_connections.
If the above tuning tips are reviewed and applied, but the server is still is encountering high “async network io” times, then ensure there aren’t any network related issues:
•Review counters such as ‘Batch Requests per second’. Any values over 3000 for a 100MB network card are getting excessive. ‘Current Bandwidth’ on the Network Interface with values over 0.6 are getting excessive.
•Check network adapter bandwidth - 1 Gigabit is better than 100 megabits and 100 megabits is better than 10 megabits.
•Look at your NIC configuration on the server to make sure there are no issues with the physical card. Also, check if autodetect is picking the fastest speed.
•Validate all of the network components between the client application and the SQL Server instance (e.g. switches / routers).
Conclusion
When a session waits on the "async network io” event, it may be encountering network issues. More likely, however, it may be an issue with the client application not processing the data quickly enough. If the wait times for “async network io” are high, review the client application to see if large results sets are being sent to the client. If they are, work with the developers to understand if all the data is needed and reduce the size of result set if possible. Ensure that any data filtering is performed in SQL Server instead of the client by utilizing views or more specific where clauses. Use the ‘TOP n’ clause so that only the rows that the client needs are returned to the client. Investigate network issues, if client application tuning tips do not apply.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply