July 30, 2012 at 3:16 pm
hello,
we are on:
Microsoft SQL Server 2005 - Ent Ed 32-bit SP4
mutltiple dbs running used for web applications
On Jun-9:
- SP4 was applied and
- max memory was set to 10GB (out of 12GB)
Starting Jun-29 we started seeing these messages frequently:
AppDomain 243 (db_name.dbo[runtime].242) is marked for unload due to memory pressure
And on Jul-10 it finally reached "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 589824" and "There is insufficient system memory to run this query" and server had to be rebooted.
We are still continually receiving the "marked for unload due to memory pressure" messages (10 times already for today).
Is this the possible cause of the system running out of memory?
and if so, how can we prevent it from happening again?
we ran perfmon for 2 days and observed that:
AvailableMbytes is good (did not dip below 8 GB)
memory grants pending - remained at 0
what else can we look at to determine the root cause of the system running out of memory?
thank you
July 30, 2012 at 3:18 pm
Memory is being flushed. You can try enabling "Lock Pages in Memory"
This should help.
I am curious if you are running a bunch of other applications on the servers (other than SQL Server) that would require more memory.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2012 at 4:32 pm
Locked pages won't help.
You're getting mem_to_leave pressure. (the portion of the base 2GB that is reserved for thing like thread stacks, CLR, backup buffers, etc before the buffer pool is allocated)
Workaround: use the -g flag to increase the mem_to_leave that's reserved and restart SQL when the symptoms start (I've seen a case where SQL had to be restarted every 2 weeks because of this)
Solution: move to 64-bit SQL.
Has more CLR been added recently? More linked server usage?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2012 at 4:38 pm
d'oh. Thanks Gail.
I was getting this about 3 months ago on 2012 and thought I had done LPIM. But looking at notes, I realized that all I had changed was Max memory to reduce to the 2GB limit. Once I did that, the errors stopped. I couldn't do the LPIM because of the version of Windows 7 (still need to fix that). Stopping and restarting services only worked briefly for me.
I think the best avenue for this is to get to a 64 bit server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 8:09 am
hi,
Most of the 'marked for unload due to memory pressure' articles i see point to use of CLR functions. would this have eventually caused the server itself to run out of memory?
There is only SQL Server running here, with multiple small/mid-size dbs used for web applications.
thanks!
July 31, 2012 at 10:54 am
sage8 (7/31/2012)
hi,Most of the 'marked for unload due to memory pressure' articles i see point to use of CLR functions. would this have eventually caused the server itself to run out of memory?
Not on 32 bit. The limitation on mem_to_leave (384 MB by default) won't allow CLR to use more than that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2012 at 8:33 pm
Of the 12 GB RAM on the system, 10 GB is set as the max size for SQL Server.
From my understanding, the CLR functions are using memory from the 2 GB not allocated to SQL Server.
Would setting the max size for SQL Server to a lower value of 8 GB reduce the unload occurrences?
However we soon ran into insufficient memory again.
2012-07-30 19:19:36.27 spid1s AppDomain 265 (db_prodctlg.dbo[runtime].264) unloaded.
2012-07-30 19:19:59.78 spid435 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 589824
:
2012-07-30 19:19:59.82 spid435 There is insufficient system memory to run this query.
At this point I'm not sure if the memory pressure is internal or external.
what tools do you recommend we can use to identify the process consuming the memory?
thank you!
August 1, 2012 at 9:30 pm
Not on 32 bit. On 32 bit they use the Mem_to_leave area which by default is 384MB.
Setting max server memory lower will accomplish nothing on 32-bit other than to reduce the size of the buffer pool an increase free memory. It will not increase the CLR available memory (which has to come out of the 2GB virtual address space and is limited to 384MB unless you increase the size of mem_to_leave)
As I said earlier, the workaround is to use the -g flag to increase the mem_to_leave that's reserved and restart SQL when the symptoms start (I've seen a case where SQL had to be restarted every 2 weeks because of this)
The solution is to move to 64-bit SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2012 at 6:59 am
thank you, that clarifies it a lot!
what would be a healthy value to start off with?
August 10, 2012 at 5:18 am
No way to answer that one. Higher than 384, but the higher you set it the more it cuts into the portion of the buffer pool under 2GB (which is used by the plan cache and all the other SQL caches other than data)
I strongly recommend you look at moving to 64 bit SQL as a solution. The mem to leave and restarts are a mitigation, nothing more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2012 at 3:38 am
hi gail,
we finally had the downtime to implement the -g startup parameter, specifying -g 512
so although we saw much less frequent "AppDomain 2 is marked for unload due to memory pressure" messages, we still encountered "Failed Virtual Allocate Bytes" and "There is insufficient system memory to run this query" within 12 hrs of increasing MTL.
i have attached the error log for your reference.
is there something else we should be looking at?
thank you
September 16, 2012 at 4:22 am
As I said when you first posted...
GilaMonster (8/1/2012)
The workaround is to use the -g flag to increase the mem_to_leave that's reserved and restart SQL when the symptoms start (I've seen a case where SQL had to be restarted every 2 weeks because of this)The solution is to move to 64-bit SQL.
-g is not a solution it's a mitigation that will slow the appearance of the problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply