4

I have three SQL database instances on a cloud server: A, B and C and the Server Installed Physical memory (RAM) is 32GB. The size of A (data+log file) is 44GB and B (data+log file) is 41GB. We ignore C since it has no databases in it and max memory set to 500MB.

Both instances A & B has it's memory set to the default size: 2147483647 MB. Although we have had no issues this is definitely not practical.

After some research, based on database activity, i decided that instance A memory allocation should be set to 20GB and B set to 10GB. In total 30/32 of the max physical memory in the system. Below shows the actual memory consumption. I am afraid if i set the max memory it will slow down response time. Is this configuration optimal in setting the SQL Max Server Memory for such databases and how can i simulate before applying?

enter image description here

Improve this question

4 Answers 4

Reset to default
2

In my opinion you're not leaving near enough for the operating system: on a 32GB system, I'd probably leave 4GB or so. Remember that local logins, SQL jobs and SSIS packages, and 3rd-party backup software and the like all use memory outside the allocated space for the SQL engine itself. If I know there are memory-hogging SSIS packages run regularly, I'll leave even more memory for the OS.

How you split the remaining between the 3 instances is up to you. There is no (serious) risk of impact by changing these on the fly, they don't even require a SQL restart to take effect.

Don't worry about a minor difference in your planned max size vs what you see in your graph there. SQL is a hog, and will use as much memory as you give it. If you reduce the max, it will have to release some memory, but it's just giving up a portion of its local data cache. It may have to go back to disk slightly sooner for some queries, but I doubt you'll notice a difference.

EDIT: After some discussion in the comments, it doesn't appear a SQL restart is necessary, even when the MaxMem is reduced below the currently used amount.

Improve this answer
12
  • 1
    @Wes You're talking about requiring a SQL restart to take effect? I've never had to do so, but I also wasn't watching the "used memory" counter particularly closely. I'd love to see some documentation on that.
    – BradC
    May 8, 2017 at 19:52
  • 1
    Not true, can be done online. Remember, SQL uses the cache and if fully utilized you might see perf issues, lower PLE or BCHR.
    – Henrico Bekker
    May 8, 2017 at 19:54
  • 1
    I stand corrected... It WILL reduce, but may take a long time to do so:dba.stackexchange.com/a/121483/15787
    – Wes
    May 8, 2017 at 19:59
  • 1
    sqlskills.com/blogs/glenn/…
    – Henrico Bekker
    May 8, 2017 at 19:59
  • 1
    @HBK I suppose there are multiple methods then, to reduce it... Naturally over a longer period of time, clearing things like plan cache or buffer cache manually (through SQL commands), or restarting the instance service.
    – Wes
    May 8, 2017 at 20:01
4

A general recommendation by Jonathan (from SQLSkills)

reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

You can use powershell dbatools Test-DbaMaxMemory to calculate for you. The dbatools has set-DbaMaxMemory to set the max memory for you across all your servers.

I would go with Test-DbaMaxMemory to check the recommendations first and once you are comfortable, use set to set it across all your servers.

Improve this answer
0
3

What you will see is that less data can be cached and, thus, some queries will have desegregated performance. The default value for RAM just, kind of, means that SQL Server will use 'whatever it can.' Once it goes up, it won't come back down until the service is restarted as it then reserves all that RAM (because it caches data on it.)

This is my favorite resource for determining how much RAM a server really needs. My only issue with it is that it doesn't really take in to account smaller VM based machines and just assumed that you CAN throw 128gb+ at a server.

https://www.brentozar.com/archive/2014/05/much-memory-sql-server-need/

A general rule of thumb is 10%, or 8GB, dedicated to OS/Auxiliary with the rest being dedicated to SQL Server (through the MAX Ram setting.) (10% or 8GB, whichever is more.)

Improve this answer
3
  • i have my fingers crossed when i apply the memory change hopefully there will be no decrease in performance. I will check out the resource you provided.
    – Raidenlee
    May 9, 2017 at 18:40
  • Do I need to add a disclaimer to my above comment? :D
    – Wes
    May 9, 2017 at 19:09
  • 1
    No need, it's obvious when you decrease memory for SQL servers there would be a drawback of some sort :p
    – Raidenlee
    May 9, 2017 at 19:12
0

Have you done analysis to see how much actual memory each instance requires? Avoid using Task Manager to determine SQL memory requirements, there are proper perfmon metrics to use for SQL memory usage.

Improve this answer
1
  • can you suggest how do you go about analysing the memory being used?
    – Raidenlee
    Mar 9, 2020 at 15:17

Your Answer

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

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