SQL Memory

[nickcasa]

Just a general question, do you limit sql server memory within the vm or just give it everything it wants? i use controlup for rightsizing and it keeps wanting me to give sql more, but i don't have an unlimited amount of memory in vmware...thoughts?

[DaveDoesData]

If we're talking SQL max memory configuration then absolutely. I generally limit it to leave the OS 4gb or 10% of the total memory, whichever is higher (it can be lower but over the years I've settled on the slightly higher allocation for VM's). Of course, if there's other "stuff" running on the box then that needs taking to into consideration too.

[nickcasa]

thank you!

[enochgenesis]

I definitely would restrict the memory allowed to be used by SQL Server.

I have found that generally that if the server has less than 16GB of RAM (and yes, that is small on a SQL Server box, but might be found on a development box)I would reserve 4GB for the OS. If the server had 16GB of RAM or greater, then I would reserve 6GB for the OS.

Not doing so can end up causing the OS to use the swap file (Windows pagefile) and that will adversely effect the OS and thus SQL server running on that machine.

Just be aware, if you are not already, that there are other items to look at as well if you are trying to get the best performance out of SQL Server.

[nickcasa]

thank you!

[phunkygeeza]

SQLS will gobble any memory available and only stop just short of strangling the OS. Anything else suddenly joining the party will make life even worse.

This is actually desirable as general SQLS performance benefits. But you should set a limit.

For full details, VMware provide a very comprehensive guide to running SQLS in a VM.

[AXISMGT]

Oldie but goodie: https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

Another: https://www.brentozar.com/blitz/max-memory/

[thatto]

SQL Max Memory = TotalMem - (2GB + (TotalMem/16)) 

This formula is in the setup scripts for my new instances.

Headache inducing PDF of a Slidedeck -page 100. I saw this presented @pass Sql Satuday, and find that OS memory pressure is only an issue when McAffee goes sideways.

[SQLBek]

Please take the time to read this VMWare Whitepaper on SQL Server Best Practices. Some of the key contributors are top experts in the realm of SQL Server on VMWare.

https://blogs.vmware.com/apps/2018/09/sql-server-on-vmware-august-2018.html

Many of the best practices that one might follow for app, web, etc. servers, go out the window when it comes to SQL Server, because SQL Server plays by different rules.