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

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

Re: Limiting memory usage

Show all headers | View raw

Erland Sommarskog:

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

Some instances work with 192 Mb, whereas others suffocate at
384.  You may be correct: I have not seen this happen to an
instance with more than 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.

I can hard-code a different threshold into my memory
balancer.

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

Yes, I expect it to go into turtle mode and thrash the HDD
and swap file, but not to die.  Another option is to
decrease memory usage to, say, 64 Gb and stop there with an
event about an impossible memory constraint.

> And 128 MB was a workable value for SQL 2005 and maybe
> also for SQL 2008. But not for SQL 2019.

Growing heavier, aren't they?

> 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

Let us hope they pay attention.

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