[isapros] Re: MSDE Ram Rule of Thumb
- From: "Amy Babinchak" <amy@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- To: <isapros@xxxxxxxxxxxxx>
- Date: Tue, 23 May 2006 11:44:33 -0400
The ISA instance doesn't cause a problem until they switch ISA over to
using it. Kind of weird that it's already installed. It's right up there
on the pet peeve list with the wireless zero config service running on
my servers.
Amy
-----Original Message-----
From: isapros-bounce@xxxxxxxxxxxxx [mailto:isapros-bounce@xxxxxxxxxxxxx]
On Behalf Of Jim Harrison
Sent: Tuesday, May 23, 2006 9:30 AM
To: isapros@xxxxxxxxxxxxx
Subject: [isapros] Re: MSDE Ram Rule of Thumb
MSDE (SQL, really) provides per-database counters, so we can easily
limit it to ISA instances alone.
One thing we can do is if the user has chosen not to use MSDE logging,
we can uninstall the ISA instance, therby eliminating this issue from
the ISA perspective, at least.
Because of the SBS "use 'em all and let memory management sort 'em out"
installation, I disagree that memory usage trends is a viable metric.
I'll ask the SQL perf folks what metrics they recommend for SQL memory
sizing (unless Tim has some ideas), but I'll bet we can monitor the DB
activity and come up with a viable metric.
-----Original Message-----
From: isapros-bounce@xxxxxxxxxxxxx [mailto:isapros-bounce@xxxxxxxxxxxxx]
On Behalf Of Amy Babinchak
Sent: Tuesday, May 23, 2006 5:22 AM
To: isapros@xxxxxxxxxxxxx
Subject: [isapros] Re: MSDE Ram Rule of Thumb
The problem with MSDE is that SBS has too many of them and as you
pointed out they don't share memory well. If it was just MSDE from ISA,
then it's probably no big deal. But an SBS box installs with MSDE
instances for Sharepoint and SBS Monitoring as well. Add those to the
Exchange store service and you've got potential for a memory war. These
wars don't happen often but when they do they can take down the entire
server. In all the time I've been supporting SBS, it's only happened to
one of my servers twice. But once a week or so, I read about it
happening to someone on the SBS list. Since you mentioned lockdown mode,
let it be known that if you upgrade from ISA 2000 to ISA 2004 that
lockdown mode is not enabled. But if you install ISA 2004 from scratch
it is. This is probably just an SBS thing, but worth noting. I mean, SBS
is already a security compromise by design, but that was just stupid and
there's no reason for it.
The point is that most SBS boxes for the next several years will be
using MSDE and not SQL Express. ISA seems pretty tolerant of waiting to
write to the log. I've throttled it back all the way to 64MB and haven't
run into a problem on my box. But my office of 4 users is a pretty small
sample. I hesitate to try it out on my clients without having some
thought that I've at least taken an educated guess instead of picking a
random number.
Amy
-----Original Message-----
From: isapros-bounce@xxxxxxxxxxxxx [mailto:isapros-bounce@xxxxxxxxxxxxx]
On Behalf Of Thor (Hammer of God)
Sent: Monday, May 22, 2006 9:25 PM
To: isapros@xxxxxxxxxxxxx
Subject: [isapros] Re: MSDE Ram Rule of Thumb
Thing is, I really doubt you'll be able to come up with that metric; not
and
have it really mean anything or be "tight" enough to make a
difference...
About the closest I think you'll be able to come is to qualify log
entries
per second. Thing is, you don't really know (nor can one plan for) how
many
log entries per second are going to be made, or what data is being
logged.
And that does not even address user queries. Depending on the number and
type of queries being made, the memory needed will change.
The other thing that we don't know (we as users of the product in regard
to
ISA 2004/6) is under what circumstances Lockdown mode is invoked. I
mean,
we know when and why it goes into lockdown mode, but we can't "tweak"
MSDE's
memory footprint without knowing precisely what criteria ISA uses to
determine when to go into Lockdown. If we lower the max memory MSDE can
use
and subsequently have a spike in activity (thus delaying MSDE write and
ack)
to X milliseconds, will that make ISA lockdown or not? Will it stay up
with
a 50 millisecond delay but lockdown at 51?
And what is more important? Having a robust logging facility that has
enough RAM to support the load, or saving a couple of seconds for an
Exchange message to be delivered on an SBS box? Do we starve MSDE and
disable lockdown? If memory is so important, then log to a text file or
log
to a different SQL box and be done with it. Or put more RAM in the box.
Or, if it really is an issue having all that on one box, then maybe the
client is no longer a candidate for SBS.
Regardless, there are far to many variables to be able to say "here's
what
your MSDE install needs, and this is only what it needs." To me,
anyway.
Given that, you've just got to trust MSDE to give back the memory. If
it
doesn't do that properly, then it should be fixed. The real solution is
to
ensure that the product behaves the way it should behave, not limit it's
resources instead of fixing it.
But, given that MSDE had been slotted for death, SQL Express seems to be
the
best way to go anyway. It dynamically manages it's memory far better
than
MSDE. If it is taking up lots of memory, then it's because it *needs*
it.
And it gives it back when it doesn't need it without having to be asked
by
the OS.
One last question, though... I looked back but didn't see what I was
looking
for. Who says that MSDE is taking up too much memory? Has an actual
test
been done to see if it gives it back on these SBS installs? Have
services
been noticeably degraded and killing MSDE brought them back? Or did
someone
just go into taskmgr, look at the processes and think "that seems high?"
What front side testing has been done to support the need for the
back-end
solution?
t
On 5/22/06 5:34 PM, "Jim Harrison" <Jim@xxxxxxxxxxxx> spoketh to all:
> Absolutely - my point (still stands) is that we need to determine a
usable,
> discoverable metric for "Joe WizardAddict"...
> Telling them to open perfmon, select counters <blah1>, <blah2>, etc.
watch
> them for three days or until the beer evaporates....
> ..isn't going to go over well.
>
> ________________________________
>
> From: isapros-bounce@xxxxxxxxxxxxx on behalf of Amy Babinchak
> Sent: Mon 5/22/2006 4:56 PM
> To: isapros@xxxxxxxxxxxxx
> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>
>
>
> I haven't played with it but since point of SQL Express was to make
all
> of the database formats consistent wouldn't it play by the SQL rules
and
> be automatically compatible?
>
> All this talk about Express, doesn't negate my request for a rule of
> thumb on the MSDE. We still need that.
>
> Amy
>
>
> -----Original Message-----
> From: isapros-bounce@xxxxxxxxxxxxx
[mailto:isapros-bounce@xxxxxxxxxxxxx]
> On Behalf Of Jim Harrison
> Sent: Monday, May 22, 2006 7:48 PM
> To: isapros@xxxxxxxxxxxxx
> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>
> ..except that ISA 2006 still uses MSDE...
>
>
> -------------------------------------------------------
> Jim Harrison
> MCP(NT4, W2K), A+, Network+, PCG
> http://isaserver.org/Jim_Harrison/
> http://isatools.org <http://isatools.org/>
> Read the help / books / articles!
> -------------------------------------------------------
>
>
> -----Original Message-----
> From: isapros-bounce@xxxxxxxxxxxxx
[mailto:isapros-bounce@xxxxxxxxxxxxx]
> On Behalf Of Amy Babinchak
> Sent: Monday, May 22, 2006 16:14
> To: isapros@xxxxxxxxxxxxx
> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>
> I don't think that fine manual has been written yet. I would think
that
> the ISA 2006 manual writers would be considering it since SQL Express
> falls squarely into the life cycle for ISA 2006.
>
> Amy
>
>
> -----Original Message-----
> From: isapros-bounce@xxxxxxxxxxxxx
[mailto:isapros-bounce@xxxxxxxxxxxxx]
> On Behalf Of Steve Moffat
> Sent: Monday, May 22, 2006 6:57 PM
> To: isapros@xxxxxxxxxxxxx
> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>
> RTFM man, RTFM....lol
>
> -----Original Message-----
> From: isapros-bounce@xxxxxxxxxxxxx
[mailto:isapros-bounce@xxxxxxxxxxxxx]
> On Behalf Of Thomas W Shinder
> Sent: Monday, May 22, 2006 8:04 PM
> To: isapros@xxxxxxxxxxxxx
> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>
> I'm willing to give it a try -- but haven't a clue on how to implement
> it. :)
>
> Thomas W Shinder, M.D.
> Site: www.isaserver.org
> Blog: http://blogs.isaserver.org/shinder/
> Book: http://tinyurl.com/3xqb7
> MVP -- ISA Firewalls
>
>
>
>> -----Original Message-----
>> From: isapros-bounce@xxxxxxxxxxxxx
>> [mailto:isapros-bounce@xxxxxxxxxxxxx] On Behalf Of Thor (Hammer of
>> God)
>> Sent: Monday, May 22, 2006 4:19 PM
>> To: isapros@xxxxxxxxxxxxx
>> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>>
>> I'm wondering if this may be a candidate for you to upgrade your MSDE
>> to SQL Express.
>>
>> SQL Express manages memory much better than MSDE-- on my system, it
>> currently only uses 1.6 Meg of RAM. IOW, it's not waiting for
another
>
>> app to ask for the RAM if it doesn't need it.
>>
>> Anybody willing to give this one a go? I can't do it on SBS but I
can
>
>> try it on an ISA 2004 install and see how it goes...
>>
>> t
>>
>>
>> On 5/22/06 12:06 PM, "Amy Babinchak" <amy@xxxxxxxxxxxxxxxxxxxxxxxxxx>
>> spoketh to all:
>>
>>> You've described the problem exactly. Where's the guidance?
>>>
>>> Amy
>>>
>>> -----Original Message-----
>>> From: isapros-bounce@xxxxxxxxxxxxx
>> [mailto:isapros-bounce@xxxxxxxxxxxxx]
>>> On Behalf Of Thor (Hammer of God)
>>> Sent: Monday, May 22, 2006 2:17 PM
>>> To: isapros@xxxxxxxxxxxxx
>>> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>>>
>>> Yes, other than the last post Jim sent out. Apparently there is an
>>> issue where sometimes MSDN does NOT give it back. But then again, we
>
>>> don't know if the OS is even asking for it back. The KB doesn't
>>> really
>> go into *why*
>>> it
>>> happens (it could be the way ISA is asking for memory for
>> that matter)
>>> but
>>> it does specify how to limit the memory.
>>>
>>> But that just adds (I assume) to Amy's frustration as even in the
>>> presence of an article that identifies an issue with MSDE memory and
>> how to limit
>>> it
>>> there is no direction or indication of exactly what to
>> limit it _to_ and
>>> under what circumstances. The last bit about "Note The
>> optimum physical
>>> memory allocation for SQL Server may vary for your system"
>> seems to be
>>> some
>>> what of a cop-out.
>>>
>>> t
>>>
>>>
>>> On 5/22/06 10:47 AM, "Thomas W Shinder"
>> <tshinder@xxxxxxxxxxx> spoketh
>>> to
>>> all:
>>>
>>>> Well then, that is good news and argues for the NON-need
>> to tweak the
>>>> MSDE Max memory. Right?
>>>>
>>>> Thomas W Shinder, M.D.
>>>> Site: www.isaserver.org
>>>> Blog: http://blogs.isaserver.org/shinder/
>>>> Book: http://tinyurl.com/3xqb7
>>>> MVP -- ISA Firewalls
>>>>
>>>>
>>>>
>>>>> -----Original Message-----
>>>>> From: isapros-bounce@xxxxxxxxxxxxx
>>>>> [mailto:isapros-bounce@xxxxxxxxxxxxx] On Behalf Of Thor (Hammer of
>
>>>>> God)
>>>>> Sent: Monday, May 22, 2006 11:05 AM
>>>>> To: isapros@xxxxxxxxxxxxx
>>>>> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>>>>>
>>>>> Right. MSDE fires up and grabs as much memory as it thinks it
>>>>> needs from the OS. Other apps fire up, and ask the OS "dude, you
>>>>> got any good memory for me?" and the OS gives it to them if
>>>>> available. If it needs more than it has, the OS will ask MSDE for
>
>>>>> some memory back. MSDE,
>> since it was
>>>>> developed to play nice with memory, will comply. Note that all
>>>>> apps do not do this- but MSDE does.
>>>>>
>>>>> t
>>>>>
>>>>>
>>>>> On 5/22/06 8:47 AM, "Jim Harrison" <Jim@xxxxxxxxxxxx>
>> spoketh to all:
>>>>>
>>>>>> The problem is; other programs ask Windows, not other apps
>>>>> for memory
>>>>>> allocations.
>>>>>> If process A owns 512MB of 1GB and process wants 640MB,
>>>>> process B will get a
>>>>>> failure response from Windows.
>>>>>>
>>>>>>
>>>>>> -------------------------------------------------------
>>>>>> Jim Harrison
>>>>>> MCP(NT4, W2K), A+, Network+, PCG
>>>>>> http://isaserver.org/Jim_Harrison/
>>>>>> http://isatools.org <http://isatools.org/>
>>>>>> Read the help / books / articles!
>>>>>> -------------------------------------------------------
>>>>>>
>>>>>>
>>>>>> -----Original Message-----
>>>>>> From: isapros-bounce@xxxxxxxxxxxxx
>>>>> [mailto:isapros-bounce@xxxxxxxxxxxxx] On
>>>>>> Behalf Of Thomas W Shinder
>>>>>> Sent: Monday, May 22, 2006 08:14
>>>>>> To: isapros@xxxxxxxxxxxxx
>>>>>> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>>>>>>
>>>>>> Hi Jim,
>>>>>>
>>>>>> But do you know if the MSDE instance will release memory to
>>>>> other processes
>>>>>> that require it? Or is it a 'grab and hold' operation?
>>>>>>
>>>>>> Thomas W Shinder, M.D.
>>>>>> Site: www.isaserver.org
>>>>>> Blog: http://blogs.isaserver.org/shinder/
>>>>>> Book: http://tinyurl.com/3xqb7
>>>>>> MVP -- ISA Firewalls
>>>>>>
>>>>>>
>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: isapros-bounce@xxxxxxxxxxxxx
>>>>>>> [mailto:isapros-bounce@xxxxxxxxxxxxx] On Behalf Of Jim Harrison
>>>>>>> Sent: Monday, May 22, 2006 9:17 AM
>>>>>>> To: isapros@xxxxxxxxxxxxx
>>>>>>> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>>>>>>>
>>>>>>> Yep - this has been "in the wild" for some time now.
>>>>>>> What we don't have (and a reasonable request) is how to
>>>>> correlate ISA
>>>>>>> logging load with MSDE memory requirements.
>>>>>>>
>>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: isapros-bounce@xxxxxxxxxxxxx
>>>>>>> [mailto:isapros-bounce@xxxxxxxxxxxxx]
>>>>>>> On Behalf Of Steve Moffat
>>>>>>> Sent: Monday, May 22, 2006 7:02 AM
>>>>>>> To: isapros@xxxxxxxxxxxxx
>>>>>>> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>>>>>>>
>>>>>>> I bow to your superior knowledge...J
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> I came across this.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Well it turns out that the max server memory setting in
>>>>> MSDE (and SQL)
>>>>>>> defaults to 2147483647Mb!! A little more digging and I
>> found a SQL
>>>>>>> script to change this memory. Copy the following into
>>>>> notepad and save
>>>>>>> as sqlmemorylimit.sql .
>>>>>>>
>>>>>>> USE master
>>>>>>> EXEC sp_configure 'show advanced options', 1 RECONFIGURE
>>>>> WITH OVERRIDE
>>>>>>>
>>>>>>> USE master
>>>>>>> EXEC sp_configure 'max server memory (MB)', 64 RECONFIGURE WITH
>>>>>>> OVERRIDE
>>>>>>>
>>>>>>> USE master
>>>>>>> EXEC sp_configure 'show advanced options', 0 RECONFIGURE
>>>>> WITH OVERRIDE
>>>>>>>
>>>>>>> With this SQL script saved we can now run it with the following
>>>>>>> command.
>>>>>>>
>>>>>>> osql -E -S servername\MSFW -i c:\sqlmemorylimit.sql
>>>>>>>
>>>>>>> This will set the memory limit for MSDE to 64MB. Now you
>>>>> are probably
>>>>>>> thinking that 64MB is awefully low, and you are right.
>> The box in
>>>>>>> question only had 256MB ram, however I would suggest to
>> set this to
>>>>>>> 1/4 of the total amount of physical memory. As an example,
>>>>> if you have
>>>>>>> 1GB of memory, set this to 256MB.
>>>>>>>
>>>>>>> ________________________________
>>>>>>>
>>>>>>> From: isapros-bounce@xxxxxxxxxxxxx
>>>>>>> [mailto:isapros-bounce@xxxxxxxxxxxxx]
>>>>>>> On Behalf Of Thomas W Shinder
>>>>>>> Sent: Monday, May 22, 2006 11:00 AM
>>>>>>> To: isapros@xxxxxxxxxxxxx
>>>>>>> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Hi Steve,
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> There is a way to set a limit on the MSDE process, but I
>>>>> didn't think
>>>>>>> there was a requirement to do so. My impression is that
>>>>> the SBS admins
>>>>>>> think that MSDE is a runaway process because it does
>> use available
>>>>>>> memory, so it looks like it taking too much. But I think
>>>>> it releases
>>>>>>> it to other processes that require it. At least that's what I
>>>>>>> understand at this point. Maybe someone can prove it otherwise.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Thomas W Shinder, M.D.
>>>>>>> Site: www.isaserver.org <http://www.isaserver.org/>
>>>>>>> Blog: http://blogs.isaserver.org/shinder/
>>>>>>> Book: http://tinyurl.com/3xqb7
>>>>>>> MVP -- ISA Firewalls
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> ________________________________
>>>>>>>
>>>>>>> From: isapros-bounce@xxxxxxxxxxxxx
>>>>>>> [mailto:isapros-bounce@xxxxxxxxxxxxx] On Behalf Of Steve Moffat
>>>>>>> Sent: Monday, May 22, 2006 8:43 AM
>>>>>>> To: isapros@xxxxxxxxxxxxx
>>>>>>> Subject: [isapros] Re: MSDE Ram Rule of Thumb
>>>>>>>
>>>>>>> As far as I know, it will release RAM when another app / process
>
>>>>>>> requires it. I don't think there is any way to modify
>> the behavior.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> S
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> ________________________________
>>>>>>>
>>>>>>> From: isapros-bounce@xxxxxxxxxxxxx
>>>>>>> [mailto:isapros-bounce@xxxxxxxxxxxxx] On Behalf Of Amy Babinchak
>>>>>>> Sent: Sunday, May 21, 2006 8:57 PM
>>>>>>> To: isapros@xxxxxxxxxxxxx
>>>>>>> Subject: [isapros] MSDE Ram Rule of Thumb
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> In the SBS world when we switch to MSDE logging in ISA, the MSDE
>
>>>>>>> service can end up taking all available ram unless we go
>>>>> in and tell
>>>>>>> it otherwise. Is there a rule of thumb for setting the ram
>>>>> on the MSDE
>>>>>>> instance for ISA? Something like X requests per minute
>> = X mb ram?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> thanks,
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Amy
>>>>>>>
>>>>>>>
>>>>>>> All mail to and from this domain is GFI-scanned.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> All mail to and from this domain is GFI-scanned.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
>
>
> All mail to and from this domain is GFI-scanned.
>
>
>
>
>
>
> All mail to and from this domain is GFI-scanned.
>
All mail to and from this domain is GFI-scanned.
Other related posts:
