Re: Another SQL Memory Issue

I'm no SQL expert, and I'm definitely not qualified to comment on whether
applications' interactions with SQL databases are correctly written. I'm
mainly just going off of the KB articles and other MS documentation. But,
it's a commonly asked question where people are complaining about allocated
memory alerts on SBS, and have built-in SQL instances using huge amounts of
RAM. For me, I throttled the ISA logging because it was consistently using
almost a full GB, and monitoring for the same reason.

I'm not sure if that's "normal" or not, but it's pretty common, hence why
Susan and I, and some others, throttled those instances, and why PSS wrote
that blog post. I throttled WSUS 2, also, although I haven't had to with
WSUS 3.

I've seen differing behavior. My accounting and MOM databases both use much
less than the 2 GB that's set for max in each (they're using less than a GB
combined). (I'll bet you didn't know anyone ran MOM on an SBS domain).
But, my DocuShare box uses just about the whole 2 GB that's allocated for
SQL. That server has RAM to spare, and I can neither explain why SQL uses
as much as it does, or why it's not using the full max value. Other than
DocuShare itself (a document management server) nothing runs on that server
to compete with SQL, so maybe that's part of the reason.


"Tony Su" <TonySu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:920C862A-8FF9-4DF3-9663-FEDEAA18B1BF@xxxxxxxxxxxxxxxx
Although your posts are good (and support my post),
IMO these still do not explain the large allocation of SQL resources. I
still feel that since an SBS box experiences continuous and changing
loads,
the odds of SQL building to 1GB or more of RAM is slim to none if
applications are written properly.

If no KB article clearly describes how the various applications use
resources, I still suspect at least one poorly written application, and
absent available information may require configuring a monitoring
application
like PerfMon, more detailed configuration of SBS Monitoring or the product
I
use on my network GroundworkOpenSource (I deploy the VMware VM I
contributed
to the Community on Torrent).

--
Tony Su
www.su-networking.com
angry.su-networking.com (Corporate Torrent Transfer Networks on Windows)
ISA
SBS
Enterprise Mobile Solutions Architect


"Dave Nickason [SBS MVP]" wrote:

It's just how SQL is designed. See
http://support.microsoft.com/kb/321363

I was mistaken in saying that SQL defaults to half the memory installed
in
the server. It defaults to 2 GB.
http://technet.microsoft.com/en-us/library/ms178067.aspx

It seems to me that some people with SBS throttle the maximum memory
settings for instances such as WSUS and ISA logging, to prevent the
allocated memory alerts. I've done that myself with no ill effects, as
has
Susan. Other people just raise the alert threshold to 110% or more of
the
30-day average, and others still will ignore or disable the allocated
memory
alert. My SBS has 4 GB of RAM, and it was bugging me to have WSUS using
a
GB, but I probably wouldn't recommend messing with the memory setting
unless
you really feel a need to do so for some reason. I have not had any
negative effects, but to me this falls in the category of "if it aint'
broke, don't fix it." Also, apparently PSS has declined to give
recommendations for these settings, because there are too many different
scenarios for them to be comfortable making blanket recommendations.


"James" <James@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:728611EA-CD77-4353-A7DE-83FBF58ADE44@xxxxxxxxxxxxxxxx
I don't understand why SQL/CRM would use 1 gig of ram, when the
application
isn't even being used. It is installed and configured, I have created
the
adminstrator acocount, and one test user account to make sure
everything
was
working (email via exchange, report server, etc.), but it still loads
up
memory.

Also note, we are strictly using the server to host web applications
(CRM,
SharePoint, Cognos, etc.). There are no desktops or other client
computers
that connect to it in a traditional sense.

"Dave Nickason [SBS MVP]" wrote:

IMO the problem is that you have multiple SQL instances on an SBS, and
all
of them by default are probably configured to use a maximum of half
the
ram
on the box. There are situations where an application may not
technically
"need" more ram to do its job, but I might rather have more ram
available
to
that app, than to give a gig or two to WSUS, which runs all the time
but
only works for 5 minutes a month. I'd rather have the ram going to
Exchange, SharePoint, etc. than to have those user applications
competing
with things like WSUS, monitoring, and ISA logging, which don't
benefit
from
the ton of ram they want, and which don't matter to the users.


"Tony Su" <TonySu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5E2B729F-CE8E-4360-8108-C1504FC03FD9@xxxxxxxxxxxxxxxx
James,
Although the other posts have offered some interesting advice, IMO
they
only
attempt to address the symptom (excessive SQL memory allocation
without
returning resources to the pool) and don't address the source of
your
problem.

Using Process Manager or even Task Manager, you can probably
identify
in a
general sense which SQL instance is acquiring and not releasing
resources.

That application should be closely inspected, because no application
written
properly to Microsoft's "Best Practices" should ever cause this kind
of
problem, SQL is usually very good about returning unused resources
within
approx 5 minutes if other processes require those resources.

--
Tony Su
www.su-networking.com
angry.su-networking.com (Corporate Torrent Transfer Networks on
Windows)
ISA
SBS
Enterprise Mobile Solutions Architect


"James" wrote:

I am running SBS 03' SP2 with Exchange, CRM 3.0, SQL 2005, and WSS
3.0
side
by side with 2.0.

I had a problem with sqlservr (NETWORK SERVICE) hogging lots of
memory
and I
corrected that problem by using
\\.\pipe\mssql$microsoft##ssee\sql\query
and
then setting a limit on the memory SharePoint uses.

Everything works fine with the embed version of SQL, however now I
am
running into the same problem with sqlservr.exe (SYSTEM), some
times
using as
much as 1 GIG of RAM. I am pretty sure that this is the DB that
CRM
uses,
and since we havent implemented it yet, I just stop te mssqlservr
service
and
everything goes back to normal. I am definitly a beginer with SQL
and
I
dont
know how to query the database to regulate the memory.

Any thoughts?

Thanks








.

Relevant Pages

  • Re: Another SQL Memory Issue
    ... IMO these still do not explain the large allocation of SQL resources. ... It seems to me that some people with SBS throttle the maximum memory ... My SBS has 4 GB of RAM, and it was bugging me to have WSUS using a ...
    (microsoft.public.windows.server.sbs)
  • Re: Large Amount of Memory Message
    ... But wasn't really sure how to match each instance of SQL with a specific ... physical RAM - in other words, if it's using the page file. ... Memory Properties) means so I don't want to necessarily change the alert: ...
    (microsoft.public.windows.server.sbs)
  • Re: Memory Usage
    ... Keep in mind that by default, Exchange and SQL will both try to use as much ... ram as they can, ... For SQL memory usage then you might look at ... Microsoft Small Business Server Support ...
    (microsoft.public.windows.server.sbs)
  • Re: Low on Virual Memory in SBS 2003
    ... Am I supposed to be putting a cap on the maximum amount of RAM that Exchange ... I don't think a memory leak is the only explanation for this. ... Although the SQL Server has been running since the first day, ... The SQL server logs seem to be clean and I am not sure what to look ...
    (microsoft.public.windows.server.sbs)
  • Re: Low on Virual Memory in SBS 2003
    ... With a commit charge at around half of the available RAM, ... Exchange or SQL further. ... System low on virtual memory is a system leak somewhere. ...
    (microsoft.public.windows.server.sbs)