[SA 9.0.176 Pro] - Long open sessions eventually slow

	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.

	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?

	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?

	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.

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

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.

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?

	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.

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).