max sever memory

one of our DBA's claims that you can happily leave the max server memory setting to the default value, as although SQL will use lots of memory, if the OS requires OS SQL will just release it. Is this statement true? And if so, what is the point in capping the max server memory for SQL Server.
LVL 3
pma111Asked:
Who is Participating?
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.

MlandaTCommented:
You might have other applications running on that server other than SQL Server. In these cases, you do not want SQL Server and the OS taking up all the available RAM. You might leave things to chance, and hope that SQL Server will release the memory, but what if it doesnt, and you other applications suffer? In that case, it's better to set a maximum for SQL Server, then things are a lot more predictable.
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
akbCommented:
It is true that SQL will release memory if the OS needs it. The point of capping the max server memory for SQL is that if the OS or other applications need memory they will have to wait for it to be release by SQL. If it already available then the OS and other applications will perform more efficiently.
Have a look here:
https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/
and here:
http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
if the OS requires OS SQL will just release it
I'm not sure of that. If SQL Server needs the memory will hardly release it so it may take some time until he can do that.

what is the point in capping the max server memory for SQL Server
To let free memory for OS and other applications (if any in the server) or other SQL Server instances (if there's more than one installed).
0
meettechieCommented:
Below technet article from Microsoft should help you:

https://technet.microsoft.com/en-us/library/ms177455%28v=sql.105%29.aspx
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
Microsoft SQL Server

From novice to tech pro — start learning today.

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.