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.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.SQL Server Performance Tuning in a Virtual Environment

  • 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

SQL Server Performance Tuning in a Virtual Environment

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

SQL Server Performance Tuning in a Virtual Environment

VHDs vs. Passthrough:: Performance

Total Read IO’s vs. Latency

SQL Server Performance Tuning in a Virtual Environment

  • 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.

Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases.