Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2150

Re: Limiting memory usage

Show all headers | View raw

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]

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Limiting memory usage Anton Shepelev <anton.txt@g{oogle}mail.com> - 2022-05-05 12:40 +0300
  Re: Limiting memory usage Anton Shepelev <anton.txt@g{oogle}mail.com> - 2022-05-05 13:18 +0300
  Re: Limiting memory usage Erland Sommarskog <esquel@sommarskog.se> - 2022-05-05 21:28 +0200
    Re: Limiting memory usage Anton Shepelev <anton.txt@g{oogle}mail.com> - 2022-05-11 12:48 +0300
  Re: Limiting memory usage Anton Shepelev <anton.txt@g{oogle}mail.com> - 2022-07-04 14:31 +0300
    Re: Limiting memory usage Erland Sommarskog <esquel@sommarskog.se> - 2022-07-05 09:45 +0200
      Re: Limiting memory usage Anton Shepelev <antonius@freeshell.de> - 2022-07-05 12:16 +0300
        Re: Limiting memory usage Erland Sommarskog <esquel@sommarskog.se> - 2022-07-05 21:27 +0200
          Re: Limiting memory usage Anton Shepelev <anton.txt@gmail.com> - 2022-07-06 00:45 +0300

csiph-web