• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 41
  • Last Modified:

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
0
Aref101
Asked:
Aref101
  • 2
3 Solutions
 
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
 
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
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.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now