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

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

Re: Limiting memory usage

Show all headers | View raw

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> 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...
> 

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.

As for the usage decreasing when you are increasing the memory, it can be 
because the OS is signaling memory pressure, or because SQL Server is still
working with trimming the memory. The memory will not start to increase 
until there are queries that needs to drag pages into memory.

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.)

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