Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We've been experiencing strange issues for awhile now with our virtual environment running SQL Servers.

We randomly get calls from users about very poor performance on the SQL boxes. Sure enough when I look I see the CPUs are pegged at 100%. I perform a VMotion to another host and as soon as it finishes moving over to another host performance immediately returns to normal.

I've been working with the VMWare admins and they have assured me that VMotion would not affect anything on the SQL Servers. It's almost as if the move to another host is causing an execution plan change or the like. I don't understand, however, why out of nowhere CPU usage jumps through the roof unless it's a bad query plan recompile due to parameter sniffing, but I would think that a VMotion would not fix that since it's supposed to be transparent.

The VM farm is comprised of 19 Dell servers (sorry I don't know the exact model) with 2 physical sockets and 12 cores on each socket.

Has anyone else observed this behavior before? I'm wondering if it's something to do with capacity as there are some large VMs for the hosts to handle it seems (there are 14 80GB, 12 core VMs floating around). Even with those VMs on the farm I can see in the Vsphere console that the hosts aren't being over-utilized (memory does creep up to the 80% mark a lot of the time, but no ballooning).

Also, this occurs on all different versions of SQL (2008, 2008R2, 2012, and 2014).

Thanks a lot for any insight!

share|improve this question
3  
Have you tried to identify what is using CPU? If you can show your VMware admins that it's not SQL Server, then they'll have to give you a better answer. If you don't even know what is using CPU, you can sit and point fingers at each other forever (and I don't know how we can help you; guessing?) – Aaron Bertrand Feb 29 at 18:16
    
Thank you, Aaron. The CPU usage is being consumed by the SQL Server process, but the peculiar thing is after the move it magically drops to regular, acceptable levels. EDIT: I'm concerned that if a vmotion causes procs to recompile (fixing a bad query plan) wouldn't that be VMware going beyond it's intended 'boundaries'? Thanks again! – JayPhen Feb 29 at 18:37
3  
VMotion isn't going in to SQL Server and saying "hey, fix your bad query plans!" But we'd need to understand more about the vMotion move - did it move the guest to a host with completely different specs? Next time it happens, dig in and see what in SQL Server is using CPU (this means going beyond task manager). – Aaron Bertrand Feb 29 at 19:08
1  
My best guess is that you were experiencing some sort of disk I/O issue on "vm1" and VMotioning to "vm2" put you onto disks that are not getting hammered. Your CPU was pegged at 100% because it kept on queuing requests that it cannot process quickly. It's possible that a disk on "vm1" simply failed so the system is trying to recover via parity and other RAID shenanigans. – MonkeyZeus Feb 29 at 21:34
up vote 5 down vote accepted

VMWare VMotion isn't going to reboot your server, restart any services or drop caches. The VM stays live during the VMotion, so you shouldn't lose cache or plans, unless the host you are moving to is under severe memory pressure and ballooning is active.

What does happen during VMotion is increased network latency and maybe a dropped ping while migrating, but that effect is completely gone once the migration is over and should not affect CPU usage inside the guest.

However what you need to understand is that the %CPU use inside the guest is the % you are consuming from the pool of resources that has been allocated to you by the hypervisor (not the underlying CPU) so if you move from a host allotting you 4Ghz to a host allotting 2Ghz the CPU usage inside the guest would double.

There are a few performance counters you could monitor inside the guest VM to see the actual CPU time you are getting from the Hypervisor such as:

  • % Processor Time
  • Effective VM Speed in MHz
  • Host processor speed in MHz
  • Limit in MHz

See here for a start

Which could give you an idea of the actual MHz you are getting, any limitations imposed by the VMWare configuration etc.

If you have determined the Hypervisor isn't allocating enough cycles to your VM you could set reservations to guarantee a certain amount of MHz or add a cpu weight to the VM giving precedence to your VM over others.

If you have access to esxtop (not the flattened sampled averages charts from vCenter) you should keep an eye on %RDY (indicating your VM has threads waiting for a physical cpu) or %CSTP (indicating co-scheduling issues). For more information read through this yellowbrick post

EDIT:

Since you are saying the host is having other high load VM's you also need to consider that VMWare is trying to allocate resources to the most demanding VM's when configured with defaults. A sudden increase in load in another VM could have dramatic (temporary) effects on your VM's cpu allocation.

share|improve this answer
    
So, just to be clear, vMotion could never possibly cause SQL Server to lose any plans in cache, or data in the buffer pool, or drop any user connections? It's not how I remember it but I'll confess it has been a while. – Aaron Bertrand Feb 29 at 20:36
    
@AaronBertrand Unless there are severe memory pressure issues I don't see how. If the new host reclaims half of the memory resulting in cache flushes that's something else. The machine stays live and memory is copied over in lockstep. You can get a packet loss during commit phase where it actually switches over resulting in a dropped connection or 2 (and even that is rare) but no reboots. – Tom V Feb 29 at 20:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.