Take the 2-minute tour ×
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.

We're running into a strange issue in a deployed version of our product where the performance is just not very good. When we restore a backup of their DB on one of our machines, it performs as we would expect.

While there are plenty of obvious things to look at, I'm focused on one area for the moment: the number of CPU cycles is 5-7 times higher in profile traces on their machine than it is on ours - running the same queries against the same database.

How does the CPU column in SQL perfmon get calculated on a vm? Do cycles get clocked while the virtual CPU is waiting for a real one? Could the switching of the real CPU under the virtual one lead to cache loss and higher CPU numbers? Am I wasting my time here?

Details: - Both servers are on ESX hosts with SAN storage. - I believe their infrastructure may be overloaded. - VM has 4 CPUs, 16 (or more) GB ram minimal real load on either from within the VM - SAN host is running loads far less than max - ESX hosts are said to be not overloaded (although their infrastructure is expanding soon) - I will be digging into this more tomorrow.

I know there are a lot of other variables involved, but my curiosity is killing me about the huge difference in CPU numbers for the same queries on the same DB - even if it ends up not being the problem, I'd love to know more about what causes these differences!

share|improve this question
1  
Can you also provide the processor family details, please? –  RoKa May 2 '13 at 9:39
1  
Just because the queries are the same, doesn't necessarily mean the plans are and what operations are taking place. Have you capture the actual execution plans from both environments to compare? –  Thomas Stringer May 2 '13 at 13:52
add comment

1 Answer

Over-committing CPU and memory on a VM can certainly give you false positives. The true performance bottlenecks are "hidden" from the OS, so you may not see it as easily as you would on a normal windows box. I have seen this countless times before, so we always recommend reserves on assigned resources, and where possible, dedicated LUNs for the SQL Server.

Also, if your client is using "older" (for e.g. pre-Nehalem) processors, the actual virtualisation may be impacted by shortcomings of the hardware. I'd never recommend putting a SQL Server VM (especially not a high-IO one) on a host that has older hardware. Nehalem family introduced VT-d (if memory serves), which (similarly to how VT-x attaches a VM ID tag onto the TLBs, enabling the hardware to know which VM a processor call belongs to) means your hardware is optimised for directed I/O. All of these help in freeing up the hypervisor from having to keep track of these when emulating processor, memory and I/O calls.

Also, other things that may impact performance at a VM level is DRS, and VMotion, but the newer the hardware, the less of a concern they become, especially when you have VT-d, VT-c and VT-n enabled processors and chipset.

Pending feedback from you, I will be extending this answer, so please provide comments...

share|improve this answer
add comment

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.