SQL Server Performance Tuning in a Virtual Environment

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.