[isapros] Re: MSDE Ram Rule of Thumb
- From: "Thor (Hammer of God)" <thor@xxxxxxxxxxxxxxx>
- To: "isapros@xxxxxxxxxxxxx" <isapros@xxxxxxxxxxxxx>
- Date: Mon, 22 May 2006 18:25:03 -0700
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.
>
Other related posts:
