SQL Server Performance Tuning in a Virtual Environment
I have already spoken about SQL Server monitoring, SQL I/O best practices, and general SQL performance best practices. In this post, I am going to talk about SQL Server Performance Tuning in a Virtual Environment.
Guidance for Optimizing SQL Server in Hyper-V
Running SQL Server workloads within Hyper-V guest VM’s is a definitely a supported and a viable option for a production environment, with the assumption that limitations of a Guest VM have met the performance requirements of the customer workload.
- Hyper-V guest VMs are limited to a maximum of 4 virtual CPU’s (limit of 2 virtual CPU’s on Windows 2003 guest VMs). If the workloads are CPU bound, consider increasing the virtual CPUs or physical server alternatives.
- When compared against native the same throughput can be achieved within a guest VM at a cost of slightly increased CPU utilization
- Assuming comparable hardware resources, proper hardware sizing is critical to SQL Server performance.
- Ensure that cumulative physical CPU resources on a server are adequate to meet the needs the guest VMs.
- Test/Monitor your workloads
- Important to scale the performance to the total workload required of each VM(s).
I/O performance impact is minimal from VM when proper sizing and configuration is performed on the storage tier. Recommended to use Passthrough or fixed VHD.
- Hyper-V IO load balance improves performance at the storage tier.
- In scenarios, where you over-commit the CPU resources, we have observed more CPU overhead to manage the additional logical CPU’s.
- In the case of a Network Latency, workload utilizing heavy network resources may see the more CPU overhead and performance impact. Network bottlenecked workload could see lower throughput
CPU Affinity
- Not supported by Hyper-V
- SQL CPU Affinity has no practical effect on virtual instance
- Memory allocation is static for VM
- Allocate enough memory for your workload
CPU Utilization vs. Throughput
Same throughput attainable, however, there is more CPU overhead with hyper-v enabled or when running SQL Server within a VM.
IO Performance: Native vs. VM
VHDs vs. Passthrough:: Performance
Total Read IO’s vs. Latency
- VHD’s on Shared Storage vs. Dedicated Spindles using Passthrough Disks
- Measuring average reads per second vs. latency
- VHDs on common disks has slight latency overhead and less throughput
- Graph bars = Reads/sec
- Lines = Avg. Disk/sec Read (.001 = 1 ms)
I hope, this gives you a good idea about VM performance for running SQL Server Workloads.