[SA 9.0.176 Pro] - Long open sessions eventually slow

[SA 9.0.176 Pro] - Long open sessions eventually slow

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.

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?

	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.

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

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.

No such luck this morning... will keep an eye on it.

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.

Was your spid the same this morning in the tab left open since yesterday?

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

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?

	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.

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?

	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.

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.

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?