October 30, 2009 at 8:33 am
I have been looking at wait stats to try and figure out some processing time problems that I have not been able to improve with simeple index analysis and improvement, and I see the leading cause of resource waits is ASYNC_NETWORK_IO. It turns out this is not merely pointing at a network bottleneck, as the name implies. Instead, it indicates that the applications hitting the DB server are querying large results sets but not processing all those rows.
How can I go about finding which are the problem queries. In other words, how do I know what queries are pulling lots of records but only processing a few, without getting into the application code?
Is it also possible this wait type is the result of only processing rows one at a time as with cursors (one of the major apps hitting this server is cursor based)?
October 30, 2009 at 6:59 pm
Vincent Central (10/30/2009)
I have been looking at wait stats to try and figure out some processing time problems that I have not been able to improve with simeple index analysis and improvement, and I see the leading cause of resource waits is ASYNC_NETWORK_IO. It turns out this is not merely pointing at a network bottleneck, as the name implies. Instead, it indicates that the applications hitting the DB server are querying large results sets but not processing all those rows.How can I go about finding which are the problem queries. In other words, how do I know what queries are pulling lots of records but only processing a few, without getting into the application code?
Is it also possible this wait type is the result of only processing rows one at a time as with cursors (one of the major apps hitting this server is cursor based)?
1) Not sure I understand (or if I do believe) that this wait state means rows are being queried but not processed (unless something else is causing a disconnect/timeout, etc)
2) cursors that return a row at a time back to the client can indeed present lots of network io stalls. changing that code would be my first priority
3) I assume you have read the best practice document: SQL Server 2005 Waits and Queues
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 11, 2009 at 11:16 am
Yeah, I've read the waits and queues article. It's part of what I used to write a proc that returns intelligible descriptions for many of the more common waits (I'll post the code, basically just a long query with case statements, if anyone asks).
I like your idea about eliminating the cursor code, but unfortunately a large % of the work my DB servers do are for Dynamics AX, which is entirely cursor based with no re-write in the foreseeable future. :crazy:
I'll repost my question to see if anyone has an answer:
How can I go about finding which [ones] are the problem queries. In other words, how do I know what queries are pulling lots of records but only processing a few, without getting into the application code? Or, is this even possible/plausible.
November 11, 2009 at 11:37 am
The queries that, when they are run, show wait times with the async network io wait type. Query sys.dm_exec_requests to get a snapshot of what's happening. Do that repeatedly at intervals and you should catch the queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply