Enterprise TechSQL SQL Server Performance Tuning in a Virtual Environment February 23, 2016898 views0 Share 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://www.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. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server By DataGeekApril 15, 2019
Enterprise Tech How to use Azure Ad Join on Windows Server 2019? & Gateway Access Just like Microsoft Windows 10, the Microsoft Server 2019 also lets you make use of ...
Enterprise Tech What is VMware VMotion? Limitations & Requirements – Storage & Shared If you are in the IT industry, especially infrastructure professionals, you may have heard the ...
Cloud Computing Differences Between VMWare SRM Standard and Enterprise Licensing Disaster Management is one of the essential aspects of any virtualization technique. VMWare, being one ...
Enterprise Tech Cisco WebEx vs Skype for Business: Differences and Comparisons The workforce today has become completely mobile. With that change in the work strategy, the ...