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.

Disclaimer: The Questions and Answers provided on https://gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.