0

Link: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15#procedure-for-configuring-a-fixed-amount-of-memory-not-recommended

It reads: Procedure for configuring a fixed amount of memory (not recommended)

Why does it say procedure "not recommended"?

I want to upgrade the SQL server memory, so what is the recommended procedure?

3
  • 2
    I would probably open a GitHub issue on the docs site to ask for clarification. It's unclear to me why it would have that annotation. Commented Oct 6, 2021 at 11:28
  • I believe the "not recommended" configuration is setting the min & max memory the same, to give SQL Server a fixed memory allocation. The last line of that section is "It is recommended to set a max server memory as detailed above." which points to just configuring Max Server Memory
    – AMtwo
    Commented Oct 6, 2021 at 13:09
  • Ok I assumed it was about the procedure- specifically using the GUI to make this change.
    – variable
    Commented Oct 6, 2021 at 13:41

2 Answers 2

3

If you specify the same for min and max ("fixed"), then there's no "wiggle-room" for SQL Server to adjust based on what happens in the machine. For instance, a job is starting and memory is required for that job.

4
  • Why does it assume I will set the same min and max value? The not recommended is for procedure from the looks of it.
    – variable
    Commented Oct 6, 2021 at 10:03
  • 1
    Please read that section carefully. "Fixed" means setting min and max to the same value. At least that is how I read the text. Any other meaning is meaningless, since we always have a min and a max value (even if 0 and 2147483647 - those are values as well). Commented Oct 6, 2021 at 12:08
  • So suppose I set min as 0 and max as 100GB, min not equal to max. Is this becomes recommended?
    – variable
    Commented Oct 6, 2021 at 12:48
  • Then you don't have it set to "fixed". Many of us prefer to specify a max server memory, I'm sure you will find plenty of articles out there on this topic. Even the setup program for SQL Server 2019 sets a max value! Commented Oct 6, 2021 at 13:52
0

It isn't that specific procedure that is not recommended, it is manually setting the memory limits in general is not recommended.

There are circumstances where it is recommended¹² or otherwise useful³ but if you are just running one instance of SQL server and little else on the machine then you shouldn't play with these values and instead just let SQL Server and the OS deal with it. You could set them to something that is too low, you could upgrade the machine later and forget to increase the allocation, …

[1] for instance: some instances when you are running multiple things on the same server, one of which happens to be SQL Server
[2] for instance: some circumstances where you are running multiple instances of SQL Server on the same machine, as they don't cooperate to try share memory efficiently
[3] for instance: in a development/testing environment when you want to test performance in a more memory limited environment

1
  • A lot of work has been put in to make SQL Server have reasonable default behaviors, and not require a bunch of DBA configuration to run well. Because (and as a result of this) most SQL Servers run without the careful attention of an experienced DBA. Commented Oct 6, 2021 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.