Memory Pressure - can this catastrophy be avoided?

Question

Memory Pressure - can this catastrophy be avoided?

FLDaveM

SSC Enthusiast

Points: 103
More actions
November 19, 2008 at 1:18 pm

#203104

We get "unable to complete due to memory pressure" occasionally. I just now added the -g384 flag to give the CLR more room when we want to register the assemblies (either during failover or a new build). This worked, but it seems to me that it's just a matter of time before we use up that space, too.
So, my question is, like cycling error files, can we somehow clear that memory space on the live database every so often, and how? I don't know, but would failing over to the mirror and stoping and starting the sql service on the machine do it? And how can I tell if we're even approaching a memory pressure problem?
Thanks in advance,
Dave

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

The simple answer to your question is yes, you can clear the memory but the more important question that you should be asking and looking at is why you would need to? If you add the -g384 parameter and add 128MB of extra VAS to the instance and you continue to have a problem, then you need to look for memory leaks or find out why you are having this problem.

What are you doing in SQLCLR? The error you list is generally caused by VAS fragmentation, and adding 128MB into the MTL reservation, should solve that problem. The other place I have seen this most is using unsafe/unapproved assemblies with SQL CLR and clearing the caches is a bandaid hack not a solution to the problem.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I've seen instances where the garbage collection isn't aggressive enough, so some good old fashioned

disposal of objects before letting the garbage collection have to handle it....

There are also instances where streaming isn't implemented just so, which makes the object to be streamed have to be built in full before it can be streamed, which then jacks up the memory footprint when you thought you were being thrifty....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?