June 4, 2015 at 1:13 am
Hello all!
From time to time, i get the following error on my SQL Server:
AppDomain is marked for unload due to memory pressure.
and once a week this is followed by more errors like:
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Error: 701, Severity: 17, State: 135.
There is insufficient system memory in resource pool 'default' to run this query.
Error: 701, Severity: 17, State: 123.
There is insufficient system memory in resource pool 'internal' to run this query.
The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
and the server shuts down (and has to be restarted by hand).
I do not know what is the cause of this behavior. There is enough memory left for the OS and nothing else is running on that machine.
The system configuration is:
- SQL Server 2012 SP2
- computer memory: 140GB
- Memory allocated for SQL: 70GB
- 16 CPU cores
The output of DBCC MEMORYSTATUS points large values for MEMORYCLERK_SQLQERESERVATIONS (~62GB) and MEMORYCLERK_SQLBUFFERPOOL (~28GB):
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------- ----------
VM Reserved 58446548
VM Committed 2096784
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 29532664
MEMORYCLERK_SQLQERESERVATIONS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 65737288
Could you give me an advice as from where should i start my investigation or what could be the root cause of this?
Thank you,
Iana
June 4, 2015 at 8:39 am
Your configuration suggests a number of questions:
1.) Usage indicates about 58 GB out of 70 is "reserved", suggesting that with a server of this size, it might well be plausible that a query could be written that wants more than the remaining 12 GB...
2.) Only 70 GB out of 140 GB is in use for SQL Server. Is there a reason to withhold 70 GB and leave it unused ? This leade to other questions, such as is this a virtual machine? Is there some application that needs the other 70 GB ?
3.) Might be worth some weekend down time to do a memory diagnostic... as a just in case...
Just my two cents...
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 5, 2015 at 2:15 am
Hi!
@ 1) I am not sure what those 58GB are reserved for; i thought they were for query execution. So ... please excuse a dummy question: could you please clear out (or point me to another source) what VM Reserved, VM Committed, Locked Pages Allocated, SM Reserved, SM Committed and Pages Allocated mean? It's clear to me i have a huge gap here .. 🙁
@ 2) It is not a virtual machine and there is nothing else running on it. There are only 70GB set for SQL use, because normally this application was running on a 40GB machine.
@ 3) That is a good point. I'll pass it on to our vendors.
My greatest concern is not the memory pressure itself, but SQL server shutting down. Shouldn't SQL Server be able to manage memory pressure without to force itself to shut down? If there is a really expensive query, that asks for more memory than it is available, i would expect SQL server to decide not to run the query due to insufficient resources.
Thank you!
June 5, 2015 at 2:55 am
ioana-477197 (6/5/2015)
@ 1) I am not sure what those 58GB are reserved for; i thought they were for query execution. So ... please excuse a dummy question: could you please clear out (or point me to another source) what VM Reserved, VM Committed, Locked Pages Allocated, SM Reserved, SM Committed and Pages Allocated mean? It's clear to me i have a huge gap here .. 🙁
Find and watch Mark Russinovich's Windows memory videos. They're excellent, though rather complex.
VM Reserved is virtual memory addresses which have been reserved. VM Committed is virtual addresses that are backed by physical memory or the page file. In SQL's case, physical memory.
It's fairly unlikely that a query would ask for a 12GB memory grant, and even if it did SQL is capable of managing that, it'll trim the buffer pool and other caches if necessary but will more likely grant the query less than what it wants, meaning the query will spill to TempDB.
SQL should be able to handle memory pressure without crashing.
Have a look through Chapters 1 and 4 of http://www.red-gate.com/community/books/accidental-dba and see if you can set up more diagnostics
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply