performance of Virtual SQL server (Hyper-V)

Jaroslav Latal
Jaroslav Latal used Ask the Experts™
on
Dear experts, do you have experience with 2012R2 Hyper-V virtualized SQL server performance.
I'm going to assign plenty of RAM and attach physical SSD hard drive RAID10 array to virtual server, but I'm afraid about degradation of CPU performance.

Regards,
Jarda
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer
Commented:
Naturally a well designed virtual machine has about 95% performance compared to a native one...If you have doubts you can always measure it by yourself building o a test machine that can run Hyper-V with full Virtualization capabilities
Senior Database Administrator
Commented:
Hi,

With the explosion in cores, for SQL its fairly hard to stress the cpu on a modern server. Or workstation. i don't think you'll have any problems.

Most of hte machines I currently manage SQL on are virtual machines with the disk being supplied from a SAN. The ease of increasing disks or adding a new one, increasing ram etc without having major downtime (if any) is a huge management bonus.

HTH
  David
kevinhsiehNetwork Engineer
Commented:
For normal workloads CPU isn't a problem. I Mic SQL Server in with all my other VMs on 7 year old servers with no issue. Normally people have issues due to slow disk and not enough RAM.

What RAID 10 for the SSD? The reasons why RAID 5 sucks for modern disks don't apply to SSD. RAID 1 and 5 are the norm for SSD.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks for answers. Maybe one additional question about CPU cores assigned to VM throw Hyper-V. When server has 16 cores and I assign 4 (maximum) in Hyper-V manager to the virtual server, is it OK? I need to assign maximum performance to the virtual SQL server.

Regards,
Jarda
kevinhsiehNetwork Engineer

Commented:
Sure, you can assign 4 vCPU to a VM (or even more) when the host has 16 physical cores. Depending on how busy the host is, it may not speed up the VM and may slow it down. The reason being that the host will need to have 4 physical cores available before it can execute any instruction for the VM. If the host is really busy, this means that the VM will spend more time waiting for the CPU than if it had two vCPU, which would be easier to schedule than 4 vCPU.
Philip ElderTechnical Architect - HA/Compute/Storage

Commented:
When SQL is virtualized there are a number of key factors to consider:
 + IOPS needed for all databases
 + Memory required for all instances in the VM
 + Thread count required to process all database activity

Bottlenecks in our experience are as above:
 + Storage I/O
 + Memory and NUMA boundaries
 + Physical Core Count

NOTE: vCPUs = Threads to the physical host. All threads must be processed in parallel throughout the CPU pipe. If more vCPUs than physical cores on 1 CPU get assigned to the VM then CPU logic needs to bounce threads between physical CPUs. This costs in performance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial