SQL Servers and Memory Swap

[cffit]

I have several test SQL servers that I allocate 8GB of memory to. Since upgrading to ESXi v6, a couple of them alert in VEEAM ONE about "Memory Swapped" and the ESXi hosts alert on "Memory Swap Used". It seems the best thing to do is to allocate more memory to the servers. However, when I look at the memory usage in VEEAM ONE, it shows "Memory Usage" is only 2-3%. I don't know where these calculations come from, but I have to believe that at any time these servers are using more than that.

Any insight into what might be happening would be appreciated. Thanks!

[Vitaliy S.]

Christensen, are you looking at memory active or memory consumed counter for this VM? While active might be low, the actual consumed will most likely be 80-90% for VMs like SQL Server. Do you see any high write/read rate on the datastore for these VMs?

[cffit]

Yes the memory consumed is very high. What is "memory active" and how is that stat supposed to be interpreted?

[cffit]

So I gave the server 2GB more memory. Then rebooted and both the "Host Memory Swap" and "VM Memory Swap Usage" went away... for a while, now it's back. The VM this time is using very little memory. When I do a vMotion and move the VM to another host, that host then alarms on "Host Memory Swap" and the previous host clears. This VM is just a test VM and doesn't have load on it at all.

[Vitaliy S.]

Sorry for the late reply. Memory active represents the number of memory pages recently touched by the VM. It might not necessary use all memory you've allocated to it, however the consumed will always be high for these servers. When you vMotion a VM, data gets dumped to the virtual disk (that's why you see VM Memory Swap Usage). Do you see these alerts during normal activity of this VM?

Here is a brief description of all memory counters for your convenience > Memory Performance Chart

[cffit]

Yes, these are test/dev servers that usually aren't even in use, and when so, very little activity. While they just sit idle they trigger the memory swap errors for both the VM and the host that the VM is on.

[Shestakov]

Hello, there was a related discussion on the forum`s.

Memory Swap Rate is used as a last resort to reclaim memory from VMs due to the limitations on performance. If > 0, definitely need to take action.
The actions to do include: Decreasing allocated VMs memory, planing the load distributing tasks along the whole day, adding more physical memory to the host.
Thanks!

[cffit]

The ESXi host has plenty of memory. I would want to decrease the VM's allocated memory that is showing the issue?

[Shestakov]

It can help. ESXi host uses Memory Swap when other memory reclamation techniques such as TPS(transparent page sharing) and Ballooning don`t help.

Have you checked what was the Host Memory Usage when the alarm triggered? Is the alarm triggers often by the way?

Increase of VMs memory allocation will not be useful, but rather can make things worse, since it`s not VMs, but host experiences the lack of memory resources. I would generate VMware Custom Performance report for your hosts to review Memory Active, Memory Usage, Memory Swap Used metrics to get more understanding.

Here is a great document with detailed explanation.

[cffit]

I understand if giving the VMs more memory and you were over allocating on the hosts, but if your hosts have way more memory than you are using across all VMs, then why would ballooning or swap be an issue?

[Shestakov]

That`s what we are trying to figure out. Probably there were some memory pikes on the VMs residing on the host and it caused swap and ballooning.
How often do you have the alarm triggered?

[cffit]

Everyday. Both the hosts and the VMs on the hosts complain about memory balloon and swap.

Then coincidentally, I've also noticed that most of these VMs end up crashing occasionally with the error below (names are removed and replaced with markers):

Fired by event: VmDasBeingResetWithScreenshotEvent
Event description: VM_NAME on ESXI_HOST_NAME in cluster CF Cluster in CLUSTER_NAME reset by vSphere HA. Reason: VMware Tools heartbeat failure. A screenshot is saved at [STORAGE_NAME] VM_NAME/VM_NAME-1.png.
Initiated by: Not Set

[Shestakov]

I see. And what Memory Active, Memory Usage performance is shown on the host, in the time when Swap is used? It can be checked either in VeeamONE monitor or by means VMware Custom Performance report.
With that knowledge, we can guess if the reason is over-utilized or there is some memory allocation issues or anything else.

[Vitaliy S.]

Then coincidentally, I've also noticed that most of these VMs end up crashing occasionally with the error below (names are removed and replaced with markers):

Fired by event: VmDasBeingResetWithScreenshotEvent
Event description: VM_NAME on ESXI_HOST_NAME in cluster CF Cluster in CLUSTER_NAME reset by vSphere HA. Reason: VMware Tools heartbeat failure. A screenshot is saved at [STORAGE_NAME] VM_NAME/VM_NAME-1.png.
Initiated by: Not Set

When virtual machine reboots, then CPU, Memory usage spikes can definitely observed. I have just rebooted one on my VM and received high CPU Usage alarm in the vSphere Client.

[cffit]

Yes, I definitely realize that when VMs boot up they will have CPU and Memory spikes. However, these memory conditions I've mentioned remain ongoing. If I clear them after the VM has been up for some time, they immediately repopulate.

[Vitaliy S.]

Then we need to dig deeper to see what applications are doing when alarm is triggered. BTW, what are the swap rates that you can see in the alarm history?

[cffit]

The "Swap In Rate" over a week is average of 14.46KBps and Max of 307
The "Swap Out Rate" over a week is average 16.12KBps and Max of 752

I'd like to show you the graph, but it doesn't look like I can attach pictures on here.

[Shestakov]

Swap on host happens when VMs are asking too much memory from host. Swap on VMs happens when applications ask too much from VM.
If everything works as should, you probably need to migrate VMs to another host or add more memory capacity to this one. Or as Vitaliy mentioned, find out which apps demand most of the memory.
Btw, graphs can be added with Img button.