SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
FR: async_network_io wait type

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
FR: async_network_io wait type
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post FR: async_network_io wait type Reply with quote
I've run into a very specific problem. I work on a server that's on the end of a very "distant" (i.e. slow and sometimes unstable) connection. It frequently gets stuck with async_network_io wait type. Sometimes, when I run a query that yields a result in <10 sec but it's in MBs range and (supposedly) the connection quality makes it impossible to transfer that result set to the client. The session then goes into async_network_io. After a while, it gets "stale" there, and I guess, the client and the server really get out of sync. Even hours after the server has done its job the resulting data does not arrive at the client and SQL Editor still shows Executing queries... though there's nothing executing anymore (even the Session Monitor shows the session as sleeping and no longer waiting on async_network_io).

Trying to cancel the current execution is of no avail(or rather, it makes things even worse). That tab never recovers from that state, SE is then stuck displaying Canceling active queries... forever. The Connect, Refresh Cache, Execute SQL Code and Cancel Code Execution buttons and hotkeys are disabled and the tab becomes useless. I either have to close that tab or kill its connection/session from Session Monitor. That, in turn, brings its own side-effects like losing temporary tables, their contents, and values of session variables, that is, any intermediate results, that will have to be recreated again, with the risk of going into async_network_io again.

I'd like to ask for two features:
First, could you somehow detect and indicate (eg. flashing/coloring tab header or something in the status bar) if the current query/connection falls victim to async_network_io wait type?
Second, could you implement a method to pull SE out of the stupor async_network_io wait caused? Besides the obvious "kill the session yourself with the Session Manager), of course.
Fri Jan 29, 2021 5:56 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
There is planned feature for version 12 to support physically terminating threads hosting stale connections as a result of never ending Cancel operation. In other word, give logical Cancel some time to cancel the operation and if necessary to rollback the changes, and if timeout occurs, give up the wait, and terminate the tread and its connection.
Fri Jan 29, 2021 11:25 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Just sharing a thought, I don't know if it's feasible in your case, maybe you can setup some jump server closer to the remote database and pull its data in two steps. On the jump server save it to files, transfer files across the network, and them import them to the final destination
Fri Jan 29, 2021 11:29 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.