How much MemToLeave with LiteSpeed?

Hi All,
Quest recommended that we should increase MemToLeave (currently -g512) to
1024MB. 64-bit is not an option in the short-term. I am sceptical here and I
need your input.
Windows 2003 Ent.
SQL 2005 Ent. SP2 32-bit
32GB RAM
The biggest db is 500GB
NO /3GB but AWE enabled
CLR is enabled and being used (sometimes causing "Error: 6532, Severity: 16,
State: 49. .NET Framework execution was aborted by escalation policy because
of out of memory. ")
The VAS for user mode is 2GB. If we increase MemToLeave to 1GB, there will
only be 1GB VAS for all non-data objects (query plans, connections etc.).
Will we be risking the stability of the system, or causing performance
degradation to memory-intensive workloads.
Normally, I do not observe any memory pressure, but I have not had a chance
to capture DMVs, and counters when we get memory errors yet.
Thank you,
Ada
SQL Server DBA
I had memtoleave pressure on x32. other then that same as yours.
Unless you actually have severity 20 errors in your logs something like such
15:10:22.18 spid257 WARNING: Failed to reserve contiguous
memory of Size= 2162688.
15:10:22.23 spid257 Buffer Distribution: Stolen=11981 Free=389
Procedures=107278
Inram=0 Dirty=37529 Kept=0
I/O=0, Latched=59, Other=674764
15:10:22.23 spid257 Buffer Counts: Commited=832000
Target=832000 Hashed=712352
InternalReservation=436 ExternalReservation=0 Min Free=464 Visible= 335736
15:10:22.23 spid257 Procedure Cache: TotalProcs=65050
TotalPages=107278 InUsePages=227
15:10:22.23 spid257 Dynamic Memory Manager: Stolen=119259 OS
Reserved=960
OS Committed=938
OS In Use=931
Query Plan=107450 Optimizer=0
General=10359
Utilities=12 Connection=2151
15:10:22.23 spid257 Global Memory Objects: Resource=1194
Locks=133
SQLCache=8983 Replication=4
LockBytes=2 ServerGlobal=31
Xact=59
15:10:22.23 spid257 Query Memory Manager: Grants=0 Waiting=0
Maximum=242767 Available=242767
15:10:22.26 spid257 Error: 17803, Severity: 20, State: 12
(date of the error removed)
I would not mess with it. And it is not going to fix your problem anyways.
memtoleave problems are created by the applications. they do not go away
unless the apps are fixed. Because in windows there is no more room, its
memory management works this way.
In my case a lot if the issues were created by the developers of our and
3-rd party applications. .NET apps that is. By default their package is 8k. I
asked do they have any reason to use 8k versus 4k unless that if what .net
does by default. I know DBA's usually do not ask developers on this level or
just not know where is it coming from. Make your case as I did make mine, now
I have 4k for most of the connections. Anything 8k+ is going to end up in
memtoleave area. and whatever that is the developer/vendor has to provide me
a very good reason that is should.
> CLR is enabled and being used (sometimes causing "Error: 6532, Severity: 16,
> State: 49. .NET Framework execution was aborted by escalation policy because
> of out of memory. ")
this sounds like my developers... Trace it and see
You are after "existing connection" and "audit login" event and integer data
value (as well as text data, application name, ntuser, clientprocessid, spid
etc whatever you need to identify them). If you see anything like 8192 and
bigger in integer data you want to talk to the developer/vendor. Because they
have to have a better reason then .net compiles my stuff by default like such
to make a mess in memtoleave area and request long chunks in memory. Value in
application ".net sqlclient data provider" (default app name) is going to
have by default 8192 package size. and it does not need it, with 4k network
packages they are just perfectly fine. Do not be afraid to make your case. If
it is the vendor's even if you will open ms support case (I had one), ms sql
team is not going to fix your problem, because ms sql works the same way with
memory in windows any other application does.
Even if you increase memtoleave area it is just matter of time when it is
going to be consumed. And it is not limitless. Windows does not know any
better, x32 or x64, it is not unix (man... do I wish they would learn to
manage their memory as unix does...)
found Ken Henderson's article once that has quite useful explanation what
and where will end up.
"A memory consumer within the server initiates a memory allocation by first
creating a memory object to manage the request. When the object allocates the
request, it calls on the appropriate memory manager within the server to
fulfill the request from either the BPool or the MemToLeave region. For
requests of less than 8KB, the request is usually filled using memory from
the BPool. For requests of 8KB or more of contiguous space, the request is
usually filled using memory from the MemToLeave region. Because a single
memory object may be used to carry out multiple allocations, it's possible
for an allocation that is well below 8KB in size (including its management
overhead) to be allocated from the MemToLeave region. Consumers of memory
within the SQL Server process space are usually internal consumers, that is,
consumers or objects within the SQL Server code itself that need memory to
carry out a task, but they do not have to be. They can also be external
consumers, as I've said. Usually, these external consumers use normal Win32
memory API functions to allocate and manage memory and, therefore, allocate
space from the MemToLeave space since it is the only region within the SQL
Server process that appears to be available. However, xprocs are a special
exception. When an xproc calls the Open Data Services srv_alloc API function,
it is treated just like any other consumer within the server. Generally
speaking, srv_alloc requests for less than 8KB of memory are allocated from
the BPool. Larger allocations come from the MemToLeave space."
Can not provide the link, do not have it, just a quote.
you will be looking for "Inside SQL Server 2000's Memory Management
Facilities" article (if it was an article, not a part of any book).
Thanks, Liliya
"Ada" wrote:

> Hi All,
> Quest recommended that we should increase MemToLeave (currently -g512) to
> 1024MB. 64-bit is not an option in the short-term. I am sceptical here and I
> need your input.
> Windows 2003 Ent.
> SQL 2005 Ent. SP2 32-bit
> 32GB RAM
> The biggest db is 500GB
> NO /3GB but AWE enabled
> CLR is enabled and being used (sometimes causing "Error: 6532, Severity: 16,
> State: 49. .NET Framework execution was aborted by escalation policy because
> of out of memory. ")
> The VAS for user mode is 2GB. If we increase MemToLeave to 1GB, there will
> only be 1GB VAS for all non-data objects (query plans, connections etc.).
> Will we be risking the stability of the system, or causing performance
> degradation to memory-intensive workloads.
> Normally, I do not observe any memory pressure, but I have not had a chance
> to capture DMVs, and counters when we get memory errors yet.
> Thank you,
> Ada
> SQL Server DBA
|||Thank you, Liliya.
If a SQL backup utility is using 1GB-1.5GB of 2GB VAS, and leaving less for
the app/cache, maybe we should not even use it.
Ada
SQL Server DBA
"Liliya Huff" wrote:
[vbcol=seagreen]
> I had memtoleave pressure on x32. other then that same as yours.
> Unless you actually have severity 20 errors in your logs something like such
> 15:10:22.18 spid257 WARNING: Failed to reserve contiguous
> memory of Size= 2162688.
> 15:10:22.23 spid257 Buffer Distribution: Stolen=11981 Free=389
> Procedures=107278
> Inram=0 Dirty=37529 Kept=0
> I/O=0, Latched=59, Other=674764
> 15:10:22.23 spid257 Buffer Counts: Commited=832000
> Target=832000 Hashed=712352
> InternalReservation=436 ExternalReservation=0 Min Free=464 Visible= 335736
> 15:10:22.23 spid257 Procedure Cache: TotalProcs=65050
> TotalPages=107278 InUsePages=227
> 15:10:22.23 spid257 Dynamic Memory Manager: Stolen=119259 OS
> Reserved=960
> OS Committed=938
> OS In Use=931
> Query Plan=107450 Optimizer=0
> General=10359
> Utilities=12 Connection=2151
> 15:10:22.23 spid257 Global Memory Objects: Resource=1194
> Locks=133
> SQLCache=8983 Replication=4
> LockBytes=2 ServerGlobal=31
> Xact=59
> 15:10:22.23 spid257 Query Memory Manager: Grants=0 Waiting=0
> Maximum=242767 Available=242767
> 15:10:22.26 spid257 Error: 17803, Severity: 20, State: 12
> (date of the error removed)
> I would not mess with it. And it is not going to fix your problem anyways.
> memtoleave problems are created by the applications. they do not go away
> unless the apps are fixed. Because in windows there is no more room, its
> memory management works this way.
> In my case a lot if the issues were created by the developers of our and
> 3-rd party applications. .NET apps that is. By default their package is 8k. I
> asked do they have any reason to use 8k versus 4k unless that if what .net
> does by default. I know DBA's usually do not ask developers on this level or
> just not know where is it coming from. Make your case as I did make mine, now
> I have 4k for most of the connections. Anything 8k+ is going to end up in
> memtoleave area. and whatever that is the developer/vendor has to provide me
> a very good reason that is should.
> this sounds like my developers... Trace it and see
> You are after "existing connection" and "audit login" event and integer data
> value (as well as text data, application name, ntuser, clientprocessid, spid
> etc whatever you need to identify them). If you see anything like 8192 and
> bigger in integer data you want to talk to the developer/vendor. Because they
> have to have a better reason then .net compiles my stuff by default like such
> to make a mess in memtoleave area and request long chunks in memory. Value in
> application ".net sqlclient data provider" (default app name) is going to
> have by default 8192 package size. and it does not need it, with 4k network
> packages they are just perfectly fine. Do not be afraid to make your case. If
> it is the vendor's even if you will open ms support case (I had one), ms sql
> team is not going to fix your problem, because ms sql works the same way with
> memory in windows any other application does.
> Even if you increase memtoleave area it is just matter of time when it is
> going to be consumed. And it is not limitless. Windows does not know any
> better, x32 or x64, it is not unix (man... do I wish they would learn to
> manage their memory as unix does...)
>
> found Ken Henderson's article once that has quite useful explanation what
> and where will end up.
> "A memory consumer within the server initiates a memory allocation by first
> creating a memory object to manage the request. When the object allocates the
> request, it calls on the appropriate memory manager within the server to
> fulfill the request from either the BPool or the MemToLeave region. For
> requests of less than 8KB, the request is usually filled using memory from
> the BPool. For requests of 8KB or more of contiguous space, the request is
> usually filled using memory from the MemToLeave region. Because a single
> memory object may be used to carry out multiple allocations, it's possible
> for an allocation that is well below 8KB in size (including its management
> overhead) to be allocated from the MemToLeave region. Consumers of memory
> within the SQL Server process space are usually internal consumers, that is,
> consumers or objects within the SQL Server code itself that need memory to
> carry out a task, but they do not have to be. They can also be external
> consumers, as I've said. Usually, these external consumers use normal Win32
> memory API functions to allocate and manage memory and, therefore, allocate
> space from the MemToLeave space since it is the only region within the SQL
> Server process that appears to be available. However, xprocs are a special
> exception. When an xproc calls the Open Data Services srv_alloc API function,
> it is treated just like any other consumer within the server. Generally
> speaking, srv_alloc requests for less than 8KB of memory are allocated from
> the BPool. Larger allocations come from the MemToLeave space."
> Can not provide the link, do not have it, just a quote.
> you will be looking for "Inside SQL Server 2000's Memory Management
> Facilities" article (if it was an article, not a part of any book).
> --
> Thanks, Liliya
>
> "Ada" wrote:
|||That does not sound right... open a ticket with them. my backup systems do
not behave this way.
what version are you running? I'm on 4.6 and did not find any later ones to
be acceptable or stable on x32. I have good amount of tickets open with them.
Thanks, Liliya
"Ada" wrote:

> Thank you, Liliya.
> If a SQL backup utility is using 1GB-1.5GB of 2GB VAS, and leaving less for
> the app/cache, maybe we should not even use it.
> Ada
> --
> SQL Server DBA
>