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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
VijayAuthor Commented:
Hi Pawan,

you mean in my scenario can i goahed with MIN 23 GB and MAX 230 GB?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VijayAuthor Commented:
Good information.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.