SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.176 Pro] - Long open sessions eventually slow
Goto page Previous  1, 2
 
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: 804
Country: United States

Post Reply with quote
gemisigo wrote:
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?


The rack where the server is, is literally 15 feet behind me, we're all wired up with gigabit ethernet. I'm also the first one in most mornings and when it exhibits this behavior sometimes I am still the only one in the building.

The query will run slow in "old" tabs, but a new tab the same query will execute and return a result immediately.

Eventually the "old" tab will start responding more appropriately, but then seemingly sporadically go back to being slow.. while again a new tab will be fast.

Assuming execution plans got dropped from memory or something, the second tab would of course run faster after running the old 'slow' tab.. but if I go back to the old tab most of the time it'll be slow still even after 2 executions (one old/one new) if that makes sense.

So it shouldn't be any execution plan problem or caching problem, it's a literally sporadic speed problem on tabs that have remained open for long periods of time.

I can't make heads or tails of what would be causing it.
Wed Nov 16, 2016 3:51 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
gemisigo wrote:
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?


But that would affect both fast and slow tabs. Am I getting it correctly?
Wed Nov 16, 2016 4:01 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
gemisigo wrote:
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?


Are there any DDL operations involved? I mean CREATE, ALTER, DROP statements?
Wed Nov 16, 2016 4:03 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
SysOp wrote:

But that would affect both fast and slow tabs. Am I getting it correctly?

Yes. And no. It would randomly make a tab fast or slow on returning the results (populating the grid). The queries were always fast, only you'd have to be patient sometimes to get the data from the server. But according to Mindflux it's not the case here.
Wed Nov 16, 2016 4:04 pm View user's profile Send private message
Mindflux



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

Post Reply with quote
SysOp wrote:
gemisigo wrote:
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?


Are there any DDL operations involved? I mean CREATE, ALTER, DROP statements?


There are two CREATE TABLE #tempTableNames
Wed Nov 16, 2016 4:04 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Quote:
There are two CREATE TABLE #tempTableNames
Code:

They may trigger internal cache refresh and then cascading refresh of the database explorer. If an "old" slow tab has many objects expanded in explorer, that may trigger additional related refreshes and internally executed queries. If a "new" fast tab doesn't have that, it should  run faster, because it has less work to do.

One  way to check that hypothesis is to disable [b]Automatic Cache Refresh [/b]option.

Wed Nov 16, 2016 5:10 pm View user's profile Send private message
Mindflux



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

Post Reply with quote
That appears to be it. All this afternoon it's been running like a top with database explorer completely collapsed. If I expand dbo and run it, it's slow.

Could we perhaps ask for an enhancement request to ignore DDL referencing #temp and ##temp type tables as they aren't even seen in Database Explorer.

*edit: this also explains why I've never seen it in SSMS! The SSMS S.A plugin doesn't detect those DDL changes(per a discussion we had in another topic).

Or at least change the order of processing to bring the results back first and let D.E refresh afterword.. or on another thread?
Wed Nov 16, 2016 5:17 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Quote:
Could we perhaps ask for an enhancement request to ignore DDL referencing #temp and ##temp type tables as they aren't even seen in Database Explorer.


Done. Ticket #SA0030132.
Wed Nov 16, 2016 6:07 pm View user's profile Send private message
Mindflux



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

Post Reply with quote
I take it back, you can see them if you have "Show system Objects" checked in Database Explorer and drill down in tempdb.

So perhaps the enhancement request should be either:

A) only refresh if the DDL scope is relevant to the Database Explorer contents
or
B) Put the D.E cache refresh on it's own thread to let it continue without impacting the user waiting on final messages and results to come back
or
C) just ignore all the temp type DDL DROP/CREATE/ALTER/etc from kicking off a cache refresh (the currently lodged request).
Wed Nov 16, 2016 6:20 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 Previous  1, 2
Page 2 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.