SQL Servers and Memory Swap

Real-time performance monitoring and troubleshooting

SQL Servers and Memory Swap

Veeam Logoby cffit » Mon Sep 21, 2015 5:33 pm

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!
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: SQL Servers and Memory Swap

Veeam Logoby Vitaliy S. » Tue Sep 22, 2015 9:25 am

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?
Vitaliy S.
Veeam Software
 
Posts: 19545
Liked: 1099 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: SQL Servers and Memory Swap

Veeam Logoby cffit » Tue Sep 22, 2015 1:44 pm

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

Re: SQL Servers and Memory Swap

Veeam Logoby cffit » Tue Sep 22, 2015 4:38 pm

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.
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: SQL Servers and Memory Swap

Veeam Logoby Vitaliy S. » Mon Sep 28, 2015 3:05 pm

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
Vitaliy S.
Veeam Software
 
Posts: 19545
Liked: 1099 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: SQL Servers and Memory Swap

Veeam Logoby cffit » Mon Sep 28, 2015 7:12 pm

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.
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: SQL Servers and Memory Swap

Veeam Logoby Shestakov » Tue Sep 29, 2015 1:46 pm

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!
Shestakov
Veeam Software
 
Posts: 4856
Liked: 394 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: SQL Servers and Memory Swap

Veeam Logoby cffit » Tue Sep 29, 2015 3:11 pm

The ESXi host has plenty of memory. I would want to decrease the VM's allocated memory that is showing the issue?
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: SQL Servers and Memory Swap

Veeam Logoby Shestakov » Wed Sep 30, 2015 11:39 am

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.
Shestakov
Veeam Software
 
Posts: 4856
Liked: 394 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: SQL Servers and Memory Swap

Veeam Logoby cffit » Wed Sep 30, 2015 1:06 pm

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?
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: SQL Servers and Memory Swap

Veeam Logoby Shestakov » Wed Sep 30, 2015 2:30 pm

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?
Shestakov
Veeam Software
 
Posts: 4856
Liked: 394 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: SQL Servers and Memory Swap

Veeam Logoby cffit » Wed Sep 30, 2015 2:39 pm

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
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: SQL Servers and Memory Swap

Veeam Logoby Shestakov » Thu Oct 01, 2015 9:36 am

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.
Shestakov
Veeam Software
 
Posts: 4856
Liked: 394 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: SQL Servers and Memory Swap

Veeam Logoby Vitaliy S. » Thu Oct 01, 2015 5:34 pm

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.
Vitaliy S.
Veeam Software
 
Posts: 19545
Liked: 1099 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: SQL Servers and Memory Swap

Veeam Logoby cffit » Thu Oct 01, 2015 5:39 pm

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.
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Next

Return to Monitoring



Who is online

Users browsing this forum: No registered users and 4 guests