Min and Max Memory in SQL Server 2016

Vijay
Vijay used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

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

Author

Commented:
Hi Pawan,

you mean in my scenario can i goahed with MIN 23 GB and MAX 230 GB?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
What if i set both Min and Max memory to 240 GB?

Author

Commented:
These are heavy transactional databases? total around 35-40 DB's
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
What if i set both Min and Max memory to 240 GB?

Max memory is the important one. We should only focus on that.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

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

Author

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?
ste5anSenior Developer

Commented:
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.
IT Engineer
Distinguished Expert 2017
Commented:
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.

Author

Commented:
Good information.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial