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: Limiting memory usage
Date: Thu, 5 May 2022 12:40:52 +0300
Organization: A noiseless patient Spider
Lines: 77
Message-ID: <20220505124052.0ca7954a09edb5ff9ad84a0f@g{oogle}mail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: reader02.eternal-september.org; posting-host="2449edb3eed30522b3b12486c78c2995"; logging-data="30138"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18ekTPrczPLQKoXbMNSRIEr98aJqydfots="
Cancel-Lock: sha1:XoZ5Oor3S00iuBG4S1FIbdVBDH8=
X-Newsreader: Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver:2124

Hello, all.

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 might share and
discuss this script later, but now I have a specific
question about the `max sever memory' parameter:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options

In order to estimate its effect on actual memory usage, I
wrote the following test script:

sp_configure 'show advanced options', 1 RECONFIGURE
CREATE TABLE #LOG( N INT IDENTITY, m_used INT, m_max INT )

DECLARE @mem_ref INT
DECLARE @mem_cur INT
DECLARE @mem_max INT

-- Exercise for the curious reader: rewrite the nested loops as a
-- single loop with a single query of sys.dm_os_process_memory:
WHILE 1=1 BEGIN
   SELECT @mem_ref = physical_memory_in_use_kb/1024
   FROM   sys.dm_os_process_memory
   SET @mem_max = @mem_ref
   WHILE 1=1 BEGIN
      SET @mem_max = @mem_max - 1
      IF @mem_max < 512 BREAK
      EXEC sp_configure 'max server memory', @mem_max
      RECONFIGURE
      WAITFOR DELAY '00:00:10'
      SELECT @mem_cur = physical_memory_in_use_kb/1024
      FROM   sys.dm_os_process_memory
      IF @mem_cur < @mem_ref BEGIN
         INSERT INTO #LOG VALUES( @mem_cur, @mem_max )
         BREAK
      END
   END
END

SELECT
   #LOG.m_used               AS  Used       ,
   #LOG.m_max                AS [Max]       ,
   #LOG.m_used - #LOG.m_max  AS [Overrun]   ,
   PREV.m_used - #LOG.m_used AS [Delta Used],
   PREV.m_max  - #LOG.m_max  AS [Delta Max]
FROM      #LOG
LEFT JOIN #LOG PREV ON PREV.N = #LOG.N - 1

And here are the typical results:

             https://pastebin.com/raw/L5BDGJ7Q
             (tab-separated table)

As you see from the Overrun column, most of the time actual
memory usage exceeds the configured limit by 31-33
megabytes, occasionally coinciding with it, at which moments
an interesting anomaly takes place: a higher memory limit
results in a futher decrease of used memory, for example: a
limit of 857 Mb caused memory usage to drop to 888 Mb, but
then a higher limit of 887 megabytes decreased usage to the
same 887 megabytes!

Is this behavior ducumented anywhere, and is there a method
of stable and predictable control of used memory?  I can
think of setting the limit to at least 34 megabytes less
than current usage, but this may depend on MSSQL version and
environment...

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