Path: csiph.com!news.mixmin.net!eternal-september.org!reader01.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Limiting memory usage
Date: Tue, 05 Jul 2022 09:45:32 +0200
Organization: Erland Sommarskog
Lines: 24
Message-ID: <XnsAECB6345F77FBYazorman@127.0.0.1>
References: <20220505124052.0ca7954a09edb5ff9ad84a0f@g{oogle}mail.com> <20220704143129.09dc28f65821d135ea404649@g{oogle}mail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: reader01.eternal-september.org; posting-host="32d600c762f4d14289d2c88f28780939"; logging-data="3848627"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19/49cf8LC3eQ9v+ie3kHfe"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:g0hpV0yRd97SEtQgSawZZqierd8=
Xref: csiph.com comp.databases.ms-sqlserver:2151

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> 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
> 
>... 
> 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. 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'm not surprised if you can set "max server memory" to higher values 
and still send your server into nirvana.