SQL Server Performance Forum – Threads Archive

Insufficient System Memory

We are hitting a crippling 701 "insufficient System Memory" error intermittently in out production environment. I haven#%92t gotten anywhere with PSS in two weeks. The error has occurred 4 times over the past two weeks, crippling our SQL server and application each time. When the error occurs it lasts for 5 to 20 minutes, causing the app to time out, refusing new connections, and a massive slow-down of anything that is running. SQL has recovered on its own two of these times. It recovered following a Kill of hundreds of threads reporting â€œSEMAPHORE WAITâ€. The most recent occurrence nailed all 16 processors at 100%. We were forced to issue shutdown with nowait. I have been monitoring Perfmon very closely; there are no symptoms that precede the error. Each occurrence captures a different query. Any of the queries, when run from Management Studio, complete in under a second. DBCC MEMORY STATUS reports all memory as being in an unstressed state. The first time the error occurred there were 10 GB still available on the server. Has anyone else experienced this problem or anything similar? We don#%92t use linked servers or table valued functions (there are known memory bugs related to each of these items) The following server and configuration has been running in production for 6 weeks with no issues: .SQL 2005 EE SP1 Post SP1 Hotfix kb918222
.Win 2003 SP1 (dedicated box)
.Quad Dual Core 3GHz
.32 GB memory
.AWE enabled
.No memory related flags in boot.ini
."Lock Pages in memory" set for SQL Startup account
.1 Instance (default)
.1,994 OLTP databases avg less than 100MB each
.1,200 active user threads on average (from connection pool of avg 4,000 concurent users) Any comments would be appraciated

Have you checked for any Virus or any issues with Hardware, Windows operating system lately.
Also check are there any recent changes to the infrastucture such as hotfix, service pack or firmware upgrade etc. If you have a standby server for this environment, then have you tested the application from that server. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Satya, thanks for the reply. We have checked for virus (utility runs every night). No changes have been made to OS or SQL. We do have another machine that is commited to a clustering projects and we are going to attempt to reproduce the error on that machine (same OS, SQL, and SP level). Firmware was checked 8(?) weeks ago when the server was built, but I will check again.

I just do not understand why this one causes so much confusion so one more time:
in general, applications, ie, a program that some one write, ie, SQL Server
normally does not pay attention to physical memory (SQL actuall does)
it only cares about virtual address space
let me say this loudly for every one to hear
VIRTUAL ADDRESS SPACE (VAS) so every time any process/ generates an "Insufficient Memory" or similar type message,
it means it is out of VAS, it does not matter how much physical memory you have I assume you are on W2K3 32-bit and SQL 32-bit?
try SQL startup parameter -g384 or go to 64-bit if you can see also
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://msdn2.microsoft.com/en-us/library/aa366912.aspx the SQL team was kind enough to put some DMVs for this
I did an xproc for SQL 2000, but most people are afraid to go tinkering into the SQL 2000 address space Internal virtual memory pressure
VAS consumption can be tracked by using the sys.dm_os_virtual_address_dump DMV. VAS summary can be queries using the following view. — virtual address space summary view
— generates a list of SQL Server regions
— showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1
END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
— combine all allocation according with allocation base, don’t take
into
— account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
— we shouldn’t be grouping allocations with zero allocation base
— just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size
The following queries can be used to assess VAS state. — available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0 — get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0 If the largest available region is smaller than 4 MB, we are likely to be experiencing VAS pressure. SQL Server 2005 monitors and responds to VAS pressure. SQL Server 2000 does not actively monitor for VAS pressure, but reacts by clearing caches when an out-of-virtual-memory error occurs.

joechang,
Thanks for the reply. I take it you have commented on VAS in other threads? lol. Yes, VAS is highly missunderstood. My first question to PSS was "should I use -g flag to allocate more VAS". The answer was a resounding no, do not use the -g flag w/SQL 2005. The view is helpful, thanks. I am curious as to how you computed 384? MS recommends a setting of 392 for SQL servers supporting in excess of 1500 database and further states that a larger value is required for installations using AWE (obviously we are using AWE). So why 384? There are no published recommendations for 2005 that I have been able to find.

unless i did my math wrong
default is 256
i do not like to increment to 512
so i increment by 50%
256+128 = 384?
i think some dork got their math wrong and got 392 who responded from PSS
there are 3-4 heavy hitters there, Elmore, Ward etc
then there are front line guys who can answer the phone and point to kb articles,
sometimes they hear things but don’t fully understand it. i don’t know why -g is not recommended for 2005
do they think their new garbage collector in 2000 is so wonderful?
get real, any time gc kicks in, your app will die

I am confused with VAS and memotoleave…
Is memtoleave memory and VAS are the same? http://blogs.msdn.com/slavao/archive/2006/03/14/551394.aspx
http://blogs.msdn.com/slavao/archive/2006/03/14/551394.aspx sgoodwi3: Is your server uses any third party dlls, linked server calls, CLR functions/procedures? MohammedU.
Moderator
SQL-Server-Performance.com

Joe,
Thanks for your explanation on the VAS & Memory usage in SQL 2000 & 2005, I might blog this if you are ok. Thats a good question Mohammed asking about CLR function and procedures, as it talks here in KBAhttp://support.microsoft.com/kb/913668 for more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

MohammedU, we don’t use any third party dlls, linked server calls, CLR functions/procedures. Thanks for the comment.

We have the memtoleave memory issue on SQL 2000 SP4…
As per MS it is due to linked server calls and/or third party dlls…
Read the following article..it may not be related to you…
The use of third-party detours or similar techniques is not supported in SQL Server
http://support.microsoft.com/?id=920925
MohammedU.
Moderator
SQL-Server-Performance.com

for MohammedU’s question on VAS and mem to leave most modern OS implement virtual memory system (does the acronym VMS mean anything to you young guys?) which many now call virtual address space because too many people cannot let go of the word "memory" so a 32-bit app has a 4G address space, normally split 2/2 or 3/1 between the app and the OS
Win 64 actually implements 16T address space split 8/8 instead of the full 2^64 for reasons i do not want to explain here anyways, an application can normally only request VAS, not physical memory (lets disregard the discussion on lock pages in memory from the other post for now) i am digressing, so the 32-bit SQL process has a 2 or 3G address space which can be mapped to physical memory (a matter handled by the OS, not the user process) SQL wants to use as much of this 2-3G that is available for data buffers as possible
some where in BOL is a picture of the SQL process address space, it might be in Kalen book too what it comes down to is that everything needs VAS as working space,
most notorious are xprocs, but hash joins, table variables and other stuff too so by default SQL 32-bit sets aside 256M for other stuff that will not be allocated to data buffers
thats why SQL frequently uses 1.7 or 2.7GB memory depending on /3G
most of the 256M set aside want not used so mem-to-leave is not really physical memory to leave, but rather VAS-to-leave

also, the limited VAS is a strong reason not to use CLR on 32-bit SQL
or atleast be very very careful about the VAS usage and be an expert on the .NET garbage collector personally, it think DB’s using CLR on 32-bit will have a strong preponderance to choke and die
when the laws of natural selection are at work, i should not interfere

Explained very very…
Thanks Joe. MohammedU.
Moderator
SQL-Server-Performance.com

]]>