Path: csiph.com!aioe.org!Aby9p5R+Yqq7QceeVjZLIA.user.46.165.242.75.POSTED!not-for-mail
From: Anton Shepelev <antonius@freeshell.de>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Limiting memory usage
Date: Tue, 5 Jul 2022 12:16:25 +0300
Organization: Aioe.org NNTP Server
Message-ID: <20220705121625.c2cbb88631fe0a28b8c1e5b0@freeshell.de>
References: <20220505124052.0ca7954a09edb5ff9ad84a0f@g{oogle}mail.com> <20220704143129.09dc28f65821d135ea404649@g{oogle}mail.com> <XnsAECB6345F77FBYazorman@127.0.0.1>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: gioia.aioe.org; logging-data="48237"; posting-host="Aby9p5R+Yqq7QceeVjZLIA.user.gioia.aioe.org"; mail-complaints-to="abuse@aioe.org";
Keywords: 851906414
X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
X-Notice: Filtered by postfilter v. 0.9.2
Xref: csiph.com comp.databases.ms-sqlserver:2152

Erland Sommarskog to Anton Shepelev:

> > I continue to experiment with my memory balancer, and
> > its worst defect is that sometimes by decreasing
> > 'max server memory' it causes the isntance to freeze and
> > become unresponsive until restarted.  There are three
> > ranges of these setting:
> >
> >       freeze    high CPU      Works OK, zero CPU
> >       until     usage while   usage while idle
> >       restart   idle
> >      |--------|-------------|--------------------->
> >      0        V1            V2   max server memory
> >
> > where V1 and V2 are idiosyncratic to each instance.  I
> > have tried to estimate V1 and V2 using:
> > [...]
> > Have you an idea how to estimate minimum working value
> > for server memory, that is V2 in the diagram above?
>
> You don't say which absolute values you are working with.

Deliberately, because I want to arrive at a universal
approach that will not depend on hard-coded values of V1 and
V2 but will rather estimate them.  In my case, V1 and V2 are
usually between 190 and 350 Mb.  My databases are usually
between 5-50 Gb, 5-15 databases per instance, and I see no
correlation between DB size and minimum required RAM...

> What I've noticed is that if you set "max server memory"
> to really low values, below 400 MB, SQL Server will not
> even start.

I have had that, too.  Most of the times, however, it can be
resurrected:

  1.  rename all the .mdf files for user DBs
      (e.g. to .mdf1),
  2.  start the server (with -m if necessary),
  3.  set 'max server memory' to a working value,
  4.  stop the instance,
  5.  restore the correct names of the .mdf files,
  6.  start the instance.

> I'm not surprised if you can set "max server memory" to
> higher values and still send your server into nirvana.

And I am surprised, because an enterprise system should
either refuse a setting that sends it into limbo or comply
with it even at the expense of a terribly slow performace.
Becoming unresponsive and unable to start is bad manners,
especially because setting server memory requires that the
instance be running!  The documentation says the minimum
value is 128 Mb.

I am here in search of suggestions how to detect the
situation between V1 and V2, when the instance still works,
but is on the verge of swooning.  With the plethora of
performance monitoring facilities in MSSQL, it must be
possible somehow.

-- 
()  ascii ribbon campaign - against html e-mail
/\  http://preview.tinyurl.com/qcy6mjc [archived]