Path: csiph.com!news.mixmin.net!eternal-september.org!reader01.eternal-september.org!.POSTED!not-for-mail
From: Anton Shepelev <anton.txt@g{oogle}mail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Limiting memory usage
Date: Mon, 4 Jul 2022 14:31:29 +0300
Organization: A noiseless patient Spider
Lines: 48
Message-ID: <20220704143129.09dc28f65821d135ea404649@g{oogle}mail.com>
References: <20220505124052.0ca7954a09edb5ff9ad84a0f@g{oogle}mail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: reader01.eternal-september.org; posting-host="14ef275b08c3807e07ea3e9c6454ee25"; logging-data="3546223"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+oOARckqf0BWQKl3f1lEQ0F4lLBcrD0Ec="
Cancel-Lock: sha1:9H7wwVSxZmgkkpIYhyoFbGIcMp0=
X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver:2150

I wrote:

> We have a server with usually seven to ten active MSSQL
> instances, of which only few are intensively used at each
> moment.  Manually stopping unused instances and starting
> required ones several times a week is too burdensome for
> several reasons.  In order to improve RAM utilisation, I
> have written a simple script that tries to decrease memory
> use by instances not in active operation.
> [...]

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:

I have tried monitoring the following parameters:

   1. sys.dm_os_process_memory:
        - process_physical_memory_low
        - process_virtual_memory_low

   2. sys.dm_os_performance_counters:
        - Memory Grants Pending

   3. sys.dm_os_memory_pools:
        - free_entires_count

   4. a percentage of the total .mdf file size in the instance

but in vain.

Have you an idea how to estimate minimum working value for
server memory, that is V2 in the diagram above?

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