Groups | Search | Server Info | Keyboard shortcuts | Login | Register

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

Limiting memory usage

Show key headers only | View raw

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]

Back to comp.databases.ms-sqlserver | Previous | Next — 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