What would happen if I didn't deallocate a cursor?

[FunkyDexter]

I'll start by saying: "I'm clutching at straws here".

The background: We've been getting an odd problem here that memory usage on our production server is creeping up until it fills the page file and brings the server to a grinding halt. Restarting the server is the only thing that seems to work at that point. So far we've been unable to ascertain why. Personally, I've never seen these symptoms before. We're running SQLServer 2005 on Windows Server 2003, both fully patched and up to date - nothing special there. We're currently testing all the hardware so maybe that'll throw up some answers but I can't imagine a hardware fault behaving in this sort of cumulative way. I'm wracking my brain for anything SQL based we might have done that would cause this and, because I've never seen the symptoms before, I'm starting with all the 'odd' practices that we do here which I haven't done previously.

The thing that immediately springs to mind is that we use hordes of temp tables and cursors here. My personal view of both of these structures has always been that they're spawned by Satan and shouldn't be used on pain of death so I've always avoided using them in the past (except when absolutely neccessary) so I'm not very up on the finer points. They're very much built into the culture here, though, so I'm stuck with them. I can't imagine temp tables being the problem, they'd get cleaned up naturally when they fall out of scope, but if someone's forgotton to deallocate a cursor I imagine that might cause the problem.

I've done some googling and can find lots of info that the SqlServer resources don't get freed nothing that specifies exactly what those resources might be. I'm guessing memory is one of them but, given the time it's going to take me to weed through the entire database, I don't want to waste alot of effort on a supposition. Can anyone confirm for me that, for example, a cursor not being deallocated in an autojob running every 10 minutes would build up memory usage in this way?

Thanks, Guys
Your increasingly bemused funkster.

[leinad31]

Cursors ought to be cleaned up when session terminates... do you have long running sessions? Set a max connect duration.

As to server restart did you mean just the database or the host itself? Another possibility, I'm not sure at the moment if its applicable to SQL Server but can happen with other databases, would be orphaned threads/processes; can't track them via database tools and difficult to identify which of them can be killed to free up memory... which leaves you with server restart.

[FunkyDexter]

Thanks for the response, Leinad. I'm getting pretty desparate here so I really apreciate it.

There's a few long running sessions in overnight jobs but nothing that would coincide with this. Whatever is doing it is definitely not getting released between sessions. How sure are you that they get cleaned up though because this thread implies they might not. To be honest, I'm only questioning you because of your use of the phrase 'ought to'. Is this something you're sure of, in which case I'll take it as a given.

Restarting SQLServer seemed to free up the memory but the server still runs slow so we then restart the server as well. Not sure where that leaves me on the orphaned processes theory. There's nothing other than SQL Server running on the box though. Any tips on how to investigate this?



edit> You post got me thinking and I went and checked the db communication code in our VB.Net app (we handle all our db access through a few core routines) and I can see at least one path that would potentially fail to close it's connection. I wonder if that's resulting in sessions being left open. Definitely worth some further investigation.

[sapator]

I cannot comment on memory usage but a though is, have you tried to see what the processes running on sql are, on the activity monitor?
I used to work on an insurance company and we got lot of hang when someone as fetching a lot of data.I mean literally everything on every user was hanging.When we killed the process the sql was back.

[FunkyDexter]

Thanks for the response, sapator.

That was one of the first things I checked. I found the same job running a couple of times and it was non-critical so I turned it off. The problem still came back. Having looked at it several times we see different jobs running but no discernable pattern. We can halt those jobs and we get a bit of resource back but nothing significant. The conclusion I've drawn about that is that whatever is causing the problem has already hogged all the resource and the jobs we're looking at in the activity monitor are left fighting for the last bit of memory. They're often the final straw but the camel's back's already been broken.

[leinad31]

I cannot comment on memory usage but a though is, have you tried to see what the processes running on sql are, on the activity monitor?
I used to work on an insurance company and we got lot of hang when someone as fetching a lot of data.I mean literally everything on every user was hanging.When we killed the process the sql was back.

Your database was probably I/O bound... we experienced that a lot too until we increased database cache.

Orphaned thread/process was something mentioned on the side during my database admin training. I haven't experienced it myself.

FunkyDexter is this a web application? Or client server?

[FunkyDexter]

Orphaned thread/process was something mentioned on the side during my database admin training. I haven't experienced it myself.

Ah well, gives me something to read up on and investigate anyways.

[sapator]

Are these jobs lengthy?
Again on the insurance company he had a job that only run at night(to get all the new info, about 25gb) and sometimes the server was in a lsd state in the morning.
leinad31 the main program was written good ol cobol..mm or the other language i cannot remember now.Anyway we gave cache rearranged indexes but we haven't actually ever found a solution.
Bah, it's their problem now

[FunkyDexter]

Are these jobs lengthy?

Normally no, although if the servers ground down thay sometimes have been running for for longer than expected. This would presumably be because they've been fighting for resource so not particularly surprising.

[sapator]

Well what can i say.
If you had this problem recently then better start checking on the latest sp's created.Do you run cubes?
Also is there a peace of code somewhere that changes the Thread Pool?
I can't think of anything else at the moment.

[FunkyDexter]

better start checking on the latest sp's created

Yeah, that's what I've been doing tonight. I've found two sprocs that were ammended this week (we've got triggers in the system tables that write out to an audit table when we change a sproc - VERY handy) in which a cursor might not get deallocated. In both cases it's if there's an error (our known errors rather than crashes) it GOTOs to the end of the sproc - skipping over the deallocate. I think I need to have a word with the other devs tomorrow to see who changed these and why. Unfortunately our audit only records the sproc that was changed, not the change itself or who made it so there's still a bit of detective work to do.

Thanks for the help guys.

[sapator]

NP.
This is the worst for me.If someone changes something without notifying me.Gday.

[leinad31]

No point in singling out a developer...they might just pass the buck somewhere else (if you don't trust us with code quality then have someone review it or someone else do the revision). Just talk with their head on problem and proposed solution... have them identify the sprocs and perform relevant revision... then just do a quick pre-deployment QA of updated sprocs.

Tap their manpower instead of blaming them.

[FunkyDexter]

Don't get me wrong, it won't be a case of blame. I'm the dev team leader here so I could haul them out if I wanted to but I'd hate to create a climate where people are afraid to mess up occassionaly - I've worked under regime's like that and it just makes people afraid to do ther job effectively. Besides, I know them well enough to know they'll give themselves a much harder time than I ever could. I will, however, be taking the mickey out of them for at least a week on this one and demanding they buy cakes to make up for it. Or possibly breakfast - I've just pulled on all nighter on this.

The reason I need to have a word with them is to work out the context the sprocs run in (they're not ones I'm familiar with) so whether it's likely to have caused this and what we need to do to fix it.

[TysonLPrice]

I didn't see any mention of the transaction log...could that be filling up and causing issues?