Min and Max Memory in SQL Server 2016

Hi,
Based on below details please recommended memory setting in SQL Server 2016
This is standalone server with single instance no other apps runninng. not even SSIS/SSRS/SSAS.

please go throug the attachment.memory.JPG
VijayAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Not really good to set min memory as the total amount of available memory. You should at least reduce the min memory even I would recommend to reduce both min and max server memory. The 240GB that you said before is a good start.
0
 
ste5anSenior DeveloperCommented:
It depends on memory architecture (e.g. NUMA) and the number and size of the databases. I would use 48GB as min and 240GB as max memory.
1
 
Pawan KumarDatabase ExpertCommented:
Do not go with the default settings.

Max Memory-
In this case we go with the rule 4GB or 10% (larger one), This means we are just leaving some memory for OS(Operating System).
Max Memory should be around 230GB. Also note that this is an online setting(meaning we can change it without restarting sql)

Min Memory-
Change it from 0 to 20.Also note that this is an online setting(meaning we can change it without restarting sql)
 
Note - These settings are for starting after that you can monitor and change accordingly.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
VijayAuthor Commented:
Hi Pawan,

you mean in my scenario can i goahed with MIN 23 GB and MAX 230 GB?
0
 
VijayAuthor Commented:
What if i set both Min and Max memory to 240 GB?
0
 
VijayAuthor Commented:
These are heavy transactional databases? total around 35-40 DB's
0
 
Pawan KumarDatabase ExpertCommented:
What if i set both Min and Max memory to 240 GB?

Max memory is the important one. We should only focus on that.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What if i set both Min and Max memory to 240 GB?
That's ok as Min memory would not be immediately reserved. The Min memory configuration only says that after reaching the value set, do not release more memory than what's set.
Max memory of 240GB is really a good start since Windows OS shouldn't need more than the remaining 16GB.
0
 
VijayAuthor Commented:
Hi Virto,

When verified, in one of the SQL Server set to , Min=256 and Max=256. That total RAM in that server is 256.
Is it any issue?
0
 
ste5anSenior DeveloperCommented:
Never set the max memory to the entire memory. This will lead to memory pressure for system tasks. You may start with 252GB and see whether you have OS issues, in this case lower that limit.

From a certain standpoint you may use 252/252 for min/max. This will give a little performance, cause SQL Server will not dynamically manage memory.
0
 
VijayAuthor Commented:
Good information.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.