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

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

Re: Limiting memory usage

Show all headers | View raw

Anton Shepelev (antonius@freeshell.de) writes:
> Deliberately, because I want to arrive at a universal
> approach that will not depend on hard-coded values of V1 and
> V2 but will rather estimate them.  In my case, V1 and V2 are
> usually between 190 and 350 Mb.  My databases are usually
> between 5-50 Gb, 5-15 databases per instance, and I see no
> correlation between DB size and minimum required RAM...

Even 350 MB is too low in my opinion. I have not tested vigorously, 
but my gut feeling is that you need at least 400 MB.

And you been flipping between. let's say, 2 GB and 8 GB, I could
have had some sympathy for you. Now I only say: don't do that.
 
> And I am surprised, because an enterprise system should
> either refuse a setting that sends it into limbo or comply
> with it even at the expense of a terribly slow performace.

I can certainly sympathize with that opinion, but it may not be
trivial to implement. Say that you have a server with 2TB of
RAM, and most of that is in use. Say now that for some reason
you set "Max server memory" to 10 GB. Do you think the server
will stand up? It will certainly grind to a standstill. (Although
in the end it may not be entirely unresponsive, just unbearlingly
slow.)

But I am not sure how the engine should be able to figure that out.

On the other hand, it should not permit ridiculously low values
where it knows that it cannot even start.

> Becoming unresponsive and unable to start is bad manners,
> especially because setting server memory requires that the
> instance be running!  The documentation says the minimum
> value is 128 Mb.

Indeed, and this is also the minimum value listed in 
sys.configurations. And 128 MB was a workable value for SQL 2005
and maybe also for SQL 2008. But not for SQL 2019. 

I have raised this issue with Microsoft and that they need to raise the minimum value, and I have a feedback item here:
https://feedback.azure.com/d365community/idea/3b7f1124-6225-ec11-b6e6-000d3a4f0da0
 

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