SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.176 Pro] - Long open sessions eventually slow
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.176 Pro] - Long open sessions eventually slow
Author Message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post [SA 9.0.176 Pro] - Long open sessions eventually slow Reply with quote
So this seems to apply to SQL Editor. If I have an editor tab open overnight and come back to it in the morning and execute the query it becomes obvious the results take longer than usual to return. Even if I run the same query a few times hoping it's just a dropped query plan cache, it never speeds back up. What does fix it is opening a new tab and pasting my old tabs contents into the new tab.

I know that doing that opens a new connection and I get a new SPID at the bottom.. but I don't have this same thing happen in SSMS (I left both open overnight).

Both SQL Assistant and SSMS are set to use my ADO connection in DB Connections.
Tue Nov 15, 2016 12:25 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6484

Post Reply with quote
May I ask you to try that again? Please check your spid before you leave. When you come back next morning and run the same query again, is your spid still the same?
Tue Nov 15, 2016 5:08 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post Reply with quote
SysOp wrote:
May I ask you to try that again? Please check your spid before you leave. When you come back next morning and run the same query again, is your spid still the same?


I'll note the SPID in the editor so I don't forget.

I've noticed this on more than one occasion though. This morning I just had a chance to evaluate it briefly before moving on. As I said the fix for it seemed to be opening a new tab (new SPID) and executing there. I have no idea why the old SPID left overnight would cause slowness until you give up on that session all together.
Tue Nov 15, 2016 5:11 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6484

Post Reply with quote
I think you're going to find a different number tomorrow. Your idle connection is going to be be terminated by the server at night. Both SSMS and SA detect broken connections and reconnect automatically if they can. I don't know how exactly that's done, but perhaps it's handled differently somehow and that might be a factor in performance degradation.


Last edited by SysOp on Tue Nov 15, 2016 8:04 pm; edited 1 time in total
Tue Nov 15, 2016 7:44 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post Reply with quote
That would make sense except you'd think I'd either get a new spid or some sort of connection error message rather than what I'm seeing. It's odd.
Tue Nov 15, 2016 7:52 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post Reply with quote
No such luck this morning... will keep an eye on it.
Wed Nov 16, 2016 9:52 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post Reply with quote
Video:

http://screencast.com/t/eMFbKslgK


First execution was in a tab I had open last night (and still have open), second execution was a tab I opened this morning both running the same query.

Note: If I go back to the first tab and run it again, it's still slow. So I'm not running it and fixing a query plan and then running it on a different SPID at a faster (cached) rate.
Wed Nov 16, 2016 12:24 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6484

Post Reply with quote
Was your spid the same this morning in the tab left open since yesterday?
Wed Nov 16, 2016 2:23 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post Reply with quote
SysOp wrote:
Was your spid the same this morning in the tab left open since yesterday?


Yes it is, and still is!

One thing I do see is my Editor usually says DOMAIN\USER (SPID) down at the bottom.. the tab in question just shows the SPID now with no user identification. (but oddly, is executing fast for the moment)

*Edit: Ah HAH, it doesn't show the login context if Database Explorer is closed....
Wed Nov 16, 2016 2:27 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6484

Post Reply with quote
Could you please run select @spid in that tab if that's not to late to confirm spid matches what's in the status bar?
Wed Nov 16, 2016 3:07 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post Reply with quote
SysOp wrote:
Could you please run select @spid in that tab if that's not to late to confirm spid matches what's in the status bar?


It does.
Wed Nov 16, 2016 3:12 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6484

Post Reply with quote
So, it didn't change. Honestly not what I expected to see. Puzzling, isn't it?
Do tables that you query in that tab het any significant data updates overnight? Any overnight maintenance jobs rebuilding indexes or reorganizing files?
Wed Nov 16, 2016 3:21 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post Reply with quote
SysOp wrote:
So, it didn't change. Honestly not what I expected to see. Puzzling, isn't it?
Do tables that you query in that tab het any significant data updates overnight? Any overnight maintenance jobs rebuilding indexes or reorganizing files?


No that's the thing, the whole thing is temp tables. I mean I guess if tempdb has some sort of maintenance happening at night.. but the first thing this morning it was lightning quick and then it eventually slowed down (in that tab ONLY) and then it would be fine, then slow down again, then speed up. It's sporadic to say the least. But it only seems to happen in tabs that are open for extended periods of time and seemingly no relation to the rest of our office workload.
Wed Nov 16, 2016 3:24 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 610
Country: United States

Post Reply with quote
One thing I have noticed is the messages tab usually says this completes in about 60ms in the 'slow tab' or 'fast tab'. It almost seems like the SQL Assistant results window is just laggy to come up on tabs that have been open for a while (but, still sporadic as heck). But that's just a theory.
Wed Nov 16, 2016 3:27 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1390

Post Reply with quote
Not sure if it applies here but we had a few (two, iirc) which always reported quick run times but sometimes returned result sets after very long times. It turned out that
a) the connection had a very low bandwidth and
b) it occasionally became congested
When there was congestion the queries (seemingly) run slowly regardless of where it was run from.

Could that be the case there?
Wed Nov 16, 2016 3:47 pm 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
Goto page 1, 2  Next
Page 1 of 2

 
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.