Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / February 2008

Tip: Looking for answers? Try searching our database.

How much MemToLeave with LiteSpeed?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ada - 20 Feb 2008 23:14 GMT
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
Liliya Huff - 27 Feb 2008 16:43 GMT
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).

Signature

Thanks, Liliya

> Hi All,
>
[quoted text clipped - 23 lines]
> Ada
> SQL Server DBA
Ada - 28 Feb 2008 17:50 GMT
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
Signature

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
[quoted text clipped - 118 lines]
> > Ada
> > SQL Server DBA
Liliya Huff - 28 Feb 2008 18:35 GMT
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.

Signature

Thanks, Liliya

> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.