How much memory for SQL Server 2016 Enterprise on Stand-alone VM?

Hi,
I have a question regarding memory allocation to SQL Server 2016 Enterprise edition.

We are moving from 3 virtual machines (with Win Server 2008 R2 & SQL Server 2008 R2) to 2 virtual machines (Win Server 2012 & SQL Server 2016)
We are consolidating from 4 to 2 SQL Instances.

Current memory allocations (on SQL 2008):
Instance 1: 16GB
Instance 2: 20GB
Instance 3: 6GB
Instance 4: 6GB

These are quite busy live servers with a number of databases (back-end to web sites) & a number of SSIS packages (scheduled as Agent jobs).
I had requested 40GB for each server (8GB for OS, 32GB for SQL 2016),  however I have been told this is too much and cannot be
justified.  Memory is often flat-lined at 90% (See attached).
Microsoft state following memory requirement for any SQL 2016 edition: "At least 4 GB and should be increased as database size increases to ensure optimal performance."
Also, I have recommended that we use Enterprise versus Standard as it will confer overall system performance benefits.

Please can you make some memory recommendations for SQL Server 2016 Enterprise. Basically I need your exert opinion to help  make a case for more memory.

Regards
Arif

Memory2Memory1
LVL 1
Aref101SQL Server DBAAsked:
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.

David Johnson, CD, MVPOwnerCommented:
try reducing the memory that sql server uses.. and log your results. Sql Server will use all of the available memory, the default maximum memory is  2,147,483,647 MB

I had requested 40GB for each server (8GB for OS, 32GB for SQL 2016),  however I have been told this is too much and cannot be justified. You are currently using 48GB so you should at minimum get 24 GB/Server
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There are so many variables that effect performance, it's hard to say what you'll need (pattern of app usage, how many databases, how they are setup on disk, type of disks your using, etc).

  The only things that can be said with certainty is:

1. Memory is the best "bang for the buck" (performance enhancement) thing that you can do.
2. Memory is fairly cheap.
3. As David said, SQL will consume as much memory as it can grab.

  My suggestion would be to:

1. Make sure the sever has plenty of room for expansion.
2. Make sure you buy memory in a form that allows for easy expansion (buy larger sticks rather than many small ones).
3. As David said, cap each SQL instance to an upper memory limit.    Each SQL instance will try and manage memory with the viewpoint that it is the only instance on the box.

 By doing this, you will be in a good position to expand quickly if needed.

 and I would suggest 32GB for each server if your already at 90%.

Jim.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you're installing SSIS and/or SSAS in the same server where you'll install the SQL Server instances then you for sure will be the maximum memory possible as they will fight for the same resources.
If you don't want to have issues then install SSIS and SSAS in a separate server. SQL Server engine should be installed only in a dedicated server so no other applications can steal resources from SQL Server. This way you can reduce the needs of memory and your managers can redirect the memory money to a new server (soon they will realize that they will spend more money than the one they saved).
Anyway, if they don't invest on this, they will suffer more from the lack of performance and eventually will end up to spend more money to fix the future performance issues.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
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.