SQL Server and Minimum memory requirements

How much physical memory is required for a SQL 2012 database of 10Gigs in size and growing?    Currently the windows 2008 server R2 has 16gigs of RAM... Thought?

thanks
GorapsI.T. ManagerAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
With 64GB I would be more careful and would reserve 8GB for OS as you're using the server as file server, so 64GB - 8GB = 56GB for SQL Server (max server memory).
You don't need to set a min server memory because you don't have any other SQL Server instance that can steal memory from the actual SQL Server instance. Let it as zero and SQL Server engine will manage it by his own.
0
 
ste5anSenior DeveloperCommented:
It only depends on the data usage.

In many databases only a smaller percentage 20%-50% of the data is "hot", thus permanently required. This would mean around 2-5GB for the buffer pool can be already sufficient.

Here you should take a look at the standard report Memory Consumption at the Page life expectancy value in SSMS:

Untitled.png
which tells you how long (average) data pages are keept in the buffer pool. Higher values are better. 300 and above. The value is in seconds.

But as always: it also depends on the SLA. Being as fast as possible means having all the data in the buffer pool. So you may need more and more RAM..
0
 
yo_beeDirector of Information TechnologyCommented:
Also when you give a MSSQL 16 GB of Ram by default it will gobble it up and if you give it 32 GB it will also gobble it up.    I am assuming you are install MSSQL as a DB connection to some sort of application that you purchased.  If so, what was the venders minimum requirements and recommend?  Lots of times a vender will throw their hands up if the system does not meet the minimum requirements.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
GorapsI.T. ManagerAuthor Commented:
I am checking with the vendor right now....  I know this isnt ideal but it was the only option we had... This server is also a File / print server running SQL.


Memory
0
 
yo_beeDirector of Information TechnologyCommented:
I would not recommend running multiple roles on  the same server as your MSSQL.  I will almost guarantee that the applications vender will not recommend it as well.  I am curious to see what the app vender says as well as any other contributors to this thread.
0
 
GorapsI.T. ManagerAuthor Commented:
@yo_bee  at present this was our only option... Budget issues. :(
0
 
ste5anSenior DeveloperCommented:
In this case I would limit the max sever memory for SQL Server to 12GB or even 8GB.

btw, your current PLE shows that you don't need more RAM right now.
0
 
GorapsI.T. ManagerAuthor Commented:
If I was to upgrade memory to 64gig ... could I assign 32gig to SQL and the rest for server itself?
0
 
ste5anSenior DeveloperCommented:
Well, when budget is not a problem:

go for the 64GB and assign 16GB min memory and 48GB max memory to SQL Server.

Cause it seems that your file and print services don't have memory pressure. So the base memory is sufficient.

But: Your database service currently doesn't need any of the additional memory.
0
 
GorapsI.T. ManagerAuthor Commented:
thanks.. the reason for this post was because I thought it was being over utilised and causing a issue I am also having on this post - - -

https://www.experts-exchange.com/questions/29084479/Windows-7-Office-delays-in-Printing.html?anchor=a42476579¬ificationFollowed=204251105#a42476579
0
 
ste5anSenior DeveloperCommented:
Well, before changing RAM, I would set the SQL Server min memory to 8GB and the max memory to 12GB and restart SQL Server. Then look at your print issues.

It's safer to test this first, cause changing the hardware has the immanent risk of a hardware failure.

And the current numbers (database size and PLE) don't indicate that you need more memory for SQL Server itself.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would set the SQL Server min memory to 8GB and the max memory to 12GB and restart SQL Server.
There is no need to restart SQL Server after reconfiguring the memory. That was only true for very old SQL Server versions (2000 and before).
0
 
ste5anSenior DeveloperCommented:
?? Then you cannot measure how fast the buffer pool fills. Cause it stays full..
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes you can. You can monitor the buffer pool.
Anyway, if it's a non-production environment, a restart will be easy to manage. Problem is when working on Production environment.
0
 
GorapsI.T. ManagerAuthor Commented:
After Memory setting change:

Change
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What changes did you perform?
0
 
GorapsI.T. ManagerAuthor Commented:
set the SQL Server min memory to 8GB and the max memory to 12GB
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This will make having a worst performance. Check the PageLife expectancy how it was reduced now. It means that the buffer pool has been drastically reduced. You need more memory and not reduce the reserved memory for SQL Server.
0
 
GorapsI.T. ManagerAuthor Commented:
@Vitor  What is your recommendation???
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you can't add more RAM then let the original configuration.
If you can add more RAM, then you can set the max server memory to (total RAM - 4GB). Example, if you had 16GB more, then you'll have a total of 32GB so you can set the max server memory to 28GB (32-4).
0
 
GorapsI.T. ManagerAuthor Commented:
After I upgrade I will have 64Gigs ram....   What should be min and max be for SQL memory settings?
0
 
ste5anSenior DeveloperCommented:
This is still a PLE in the magnitude of hours..
0
 
yo_beeDirector of Information TechnologyCommented:
You have one major obstacle to over come if you stay on Windows Server 2008 R2 is that you can only put up to 32 GB of memory unless you are upgrading your server OS to 2012 or higher

Image from http://techhead.co/microsoft-windows-server-2008-and-2008-r2-maximum-memory-limits/

Ws2008r2.PNG
Here is MS reference:
https://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx#physical_memory_limits_windows_server_2008_r2
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
yo_bee, that's true for Standard Edition. Maybe the OP has the Enterprise Edition, but if don't, then the 64GB RAM wouldn't be recognized.
0
 
yo_beeDirector of Information TechnologyCommented:
That is true, but he never stated what version of 2008 R2 he is running.  I figured I would give that little tidbit of info so he does not get 64 GB of memory and is shocked that he does not see all of it 64 GB.  

If he has enterprise it is a moot point, but it is worth stating this important piece of information with what he is planning.
1
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.