Path: csiph.com!eternal-september.org!reader02.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: Wed, 11 May 2022 12:48:46 +0300
Organization: A noiseless patient Spider
Lines: 102
Message-ID: <20220511124846.9b9d3841b9f724d154f75b54@g{oogle}mail.com>
References: <20220505124052.0ca7954a09edb5ff9ad84a0f@g{oogle}mail.com> <XnsAE8EDA62DDB42Yazorman@127.0.0.1>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: reader02.eternal-september.org; posting-host="1815761ac291dd1c09454638be352e31"; logging-data="12009"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18qPc59lIc0mb4KjyWqJOdkz8/pIMU4TgA="
Cancel-Lock: sha1:Y5aZk1Z/0BVbRR7WkPVm2yroEjI=
X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver:2138

Erland Sommarskog:

> Max server memory main controls the buffer cache, which
> also is the main consumer. But there are also memory
> allocations that are outside "max server memory", so it is
> not unusual for the actual usage to exceeds the setting a
> little bit.

The documentation seems to disagree with you:

   Reconfigure the amount of memory (in megabytes) for a SQL
   Server process used by an instance of SQL Server.

When I set up a limit of 768 Mb, restarted the instance, and
put some high memory pressure on it, the actual usage never
exceeded 768 Mb:

   sys.dm_os_process_memory: physical_memory_in_use_kb/1024 = 751
   Task Manager            : Working set (memory)     /1024 = 751

I then increased the limit to 1024 Mb and imparted more
memory pressure on the instance.  The memory usage was again
below the limit:

   sys.dm_os_process_memory: physical_memory_in_use_kb/1024 = 1015
   Task Manager            : Working set (memory)     /1024 = 1015

As you see, SQL Server does not exceed its memory limit if
it is not exceeded already (which it is when decreasing the
limit below actual usage)!

> Don't make this too dynamic. I don't know for sure, but I
> would assume that max server memory are one of the options
> that clear the plan cache, and thus causes a lot of
> recompilations. (Because the amount of available memory
> affects compilation of queries.)

My script is executed every hour and causes the memory usage
of an idle SQL Server to drop by a factor of two closer to
the base level (128 Mb) during each working day. In that
sense, it is not very dynamnic.  The source is far from
publishing quality, but you are welcome to exemine it if
intersted:

sp_configure 'show advanced options', 1 RECONFIGURE

DECLARE @srv_n INT =    6 -- number of MSSQL instances
DECLARE @dec_2 INT =   10 -- number of iterations for half-decay
DECLARE @m_res INT = 2048 -- memory reserved for the OS

-- TODO: instead of c_mis, try: round down to next 32, subract 32.
DECLARE @c_mmi INT         = 128
DECLARE @c_mma INT         = 2147483647
DECLARE @c_ste INT         = 32 -- minumum memory decrement (exp.)
DECLARE @c_two FLOAT       = 2
DECLARE @c_min VARCHAR(17) = 'min server memory'
DECLARE @c_max VARCHAR(17) = 'max server memory'

DECLARE @v_low BIT,
        @p_low BIT
-- RAM values, in MB:
DECLARE @m_min INT        -- min RAM parameter
DECLARE @m_use INT        -- RAM used by this MSSQL instance
DECLARE @m_lim INT        -- target RAM limitation
DECLARE @max_l INT        -- max limit
DECLARE @N     INT

-- Active only in work hours:
IF NOT DATEPART( hour, GETDATE() ) BETWEEN 9 AND 19 GOTO Finish

SELECT @m_use  = physical_memory_in_use_kb/1024,
       @p_low  = process_physical_memory_low   ,
       @v_low  = process_virtual_memory_low
  FROM sys.dm_os_process_memory
    IF @v_low=1 OR @p_low=1 GOTO Finish
   SET @m_min  = @c_mmi
    IF @m_use <= @m_min     GOTO Finish
   SET @m_lim  = @m_min + (@m_use - @m_min) / POWER(@c_two, 1.0/@dec_2)
   SET @max_l  = @m_use - @m_use % @c_ste - 1 - @c_ste + 8
    IF @max_l  < @m_lim SET @m_lim = @max_l
    IF @m_lim  < @m_min SET @m_lim = @m_min

PRINT FORMATMESSAGE('Decreasing memory use from %i to %i', @m_use, @m_lim)

   EXEC sp_configure @c_min, 0
   EXEC sp_configure @c_max, @m_lim RECONFIGURE

SET @N = 12
WHILE @N > 0
BEGIN
   WAITFOR DELAY '00:00:05'
   SELECT @m_use = physical_memory_in_use_kb/1024,
          @p_low = process_physical_memory_low
   FROM sys.dm_os_process_memory
   IF  @p_low = 1               BREAK
   IF  @m_use < @m_lim + @c_ste BREAK
   SET @N     = @N     - 1
END

   EXEC sp_configure @c_max, @c_mma RECONFIGURE

Finish: