January 11, 2016 at 5:48 pm
Since SQL Server doesn't really release memory once it has it, how does this impact in the overall environment of a VM Host that is trying to share CPU and Memory resources among many different VMs of SQL Server?
vCPU seems to play nicely by managing the CPU resources as one instance might be busy while another is not so busy.
But Memory seems much less fungible between instances all within the same VM Host.
What advice do you have about this topic? I have been reading a few articles about it. But still have not really come to a conclusion about what is the best way to think about this. Is it something I shouldn't be thinking about? Just trust VMWare and SQL Server to play nice together? or are there landmines here?
Thanks in advance for any advice.
January 11, 2016 at 6:23 pm
Do you mean how does VMWare ( or the hypervisor) work with SQL Server inside a VM? Meaning there is a host and inside of multiple VMs, SQL Server instances are running?
There can be memory pressure, but the VM itself doesn't hold onto host memory. The hypervisor manages that. However the time to reload that memory if it's spilled to disk, results in performance slowdowns in the VM.
Short answer, the hypervisor handles it, but you do not want to overcommit memory in guests that run SQL Server. I'd minimize this, which means typically you have fewer guests when they run SQL Server than for other types of guests.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 11, 2016 at 7:39 pm
Thanks for the reply.
The issue that prompted the question is that the place I have been working the past few months has 100% of the SQL Server systems on VMWare. There are 200+ VM guests. The VM Hosts in question are dedicated purely to SQL Server with all cores on the VM Host licensed with SQL Server Enterprise Edition.
The practice here is to create a new VM for each database that comes along. It would seem to me that there is a bit of overhead with each VM needing to maintain it's own memory space and vCPU resources.
For example:
If the finance department already has 5 VM guests on the VM host and they come to us with a new app they are buying that will have a SQL Server database. Would it make more sense to suggest they park their new database on one of their existing underutilized VM guests? Or does it really not matter and VMWare is good enough at transferring around CPU and Memory resources and the sheer number of VM guests is irrelevant?
I understand that each VM needs to be resourced based on the load of those databases. The question is more geared towards the sharing of resources between multiple VM guests. I am assuming that once a SQL Server guest is using Memory resources, it doesn't really let go of those resources to the rest of the guests on the host. If that is accurate, then it would make more sense to have more databases within one guest and allow the Memory assigned to that guest be balanced among multiple databases as needed for overall performance.
Am I wrong with that assumption? More databases per guest? Or don't worry about it and spin up all the guests that everyone wants to their hearts desire?
Thanks.
January 11, 2016 at 11:49 pm
JamesMorrison (1/11/2016)
Or don't worry about it and spin up all the guests that everyone wants to their hearts desire?
Not unless there's free resources on the host. If your host has 64GB memory and you already have three guests each allocated 8GB memory and one allocated 4, you shouldn't add another guest with 8GB of memory allowed. As Steve said, don't overcommit memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2016 at 8:05 am
We reserve or "lock" the memory for our SQL/Server VMs for performance reasons. If DBServerA VM has 32 GB then we lock all 32 GB so the memory is dedicated to the VM. No problems sharing CPU resources but we like to "lock" large pages in memory and "reserve" it! Just buy enough memory for the VM Host (or the VM Hosts in your VM Cluster).
January 12, 2016 at 11:42 am
JamesMorrison (1/11/2016)
Since SQL Server doesn't really release memory once it has it, how does this impact in the overall environment of a VM Host that is trying to share CPU and Memory resources among many different VMs of SQL Server?vCPU seems to play nicely by managing the CPU resources as one instance might be busy while another is not so busy.
But Memory seems much less fungible between instances all within the same VM Host.
What advice do you have about this topic? I have been reading a few articles about it. But still have not really come to a conclusion about what is the best way to think about this. Is it something I shouldn't be thinking about? Just trust VMWare and SQL Server to play nice together? or are there landmines here?
Thanks in advance for any advice.
Actually, vCPU is harder to control and manage on SQL boxes that run on top of vMware than memory, in my opinion. This is because CPU cycles are not actually shared. And let me explain: when you assign 4 cores to a SQL vm, technically speaking, all 4 cores must be available for MSSQL when processing stuff. It is not exactly like that, but more or less. We have something called co-scheduling which adds a bit more complexity to my answer, but basically that's how it works. You can over allocated RAM on a SQL vm though, and depending of how much physical memory your Vmware host actually has, it will play nicely or not.
On my SQL boxes, I ask our Vmware admin to reserve some memory on the SQL vm and then I set the min SQL memory to that. I also lock pages in memory. But we both have reviewed all our SQL boxes and regular VMs so this is not a problem. Ultimately, I do not want MSSQL vms to suffer of memory balloon problems but other less critical stuff, hence the reason for the mem reservation.
You must work in coordination with your Vmware admin for all this. Assigning more cores than you really need, can actually makes stuff slower on your Vmware environment. And the opposite is also true: not giving enough memory to SQL can give you issues too, assuming your host can handle more.
And MSSQL will release (on VMware) memory if Vmware needs it 🙂 trust me! it's called balloon driver, and is the ugliest thing on a SQL box on Vmware, you don't want to see that in action, won't be fun.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply