Real-time performance monitoring and troubleshooting
Post Reply
cffit
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

SQL Servers and Memory Swap

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: SQL Servers and Memory Swap

Post by cffit »

Yes the memory consumed is very high. What is "memory active" and how is that stat supposed to be interpreted?
cffit
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: SQL Servers and Memory Swap

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 7328
Liked: 781 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: SQL Servers and Memory Swap

Post by cffit »

The ESXi host has plenty of memory. I would want to decrease the VM's allocated memory that is showing the issue?
Shestakov
Veteran
Posts: 7328
Liked: 781 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 7328
Liked: 781 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 7328
Liked: 781 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: SQL Servers and Memory Swap

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Servers and Memory Swap

Post by Vitaliy S. »

cffit wrote: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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: SQL Servers and Memory Swap

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: SQL Servers and Memory Swap

Post by 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
Veteran
Posts: 7328
Liked: 781 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: SQL Servers and Memory Swap

Post by 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.
Post Reply

Who is online

Users browsing this forum: No registered users and 10 guests