Optimizing SQL Server Performance with VMware vSphere

Organization/Challenge

The client is a very large financial institution, specifically a credit union, operating across multiple regions. Their strategy focuses on leveraging advanced technology to streamline operations and enhance customer service. They have implemented VMWare vSphere for server consolidation to optimize their IT infrastructure.

However, the performance of their consolidated servers did not meet expectations. They experienced transient failover events across all their SQL Server Availability Groups (AGs), which they were unable to isolate and resolve; this led to frequent application timeouts and prolonged process runtimes, significantly impacting their operational efficiency and customer satisfaction. Seeking a reliable SQL Server partner to diagnose and address these issues, the client aimed to stabilize their environment and improve overall performance.

TECHNICAL Solution

XTIVIA engaged with this client to address their critical performance issues related to SQL Server Availability Groups (AGs) on VMware vSphere. The initial phase involved a comprehensive triage of each SQL Server guest, comparing configurations against VMware’s best practices guide for architecting Microsoft SQL Server on VMWare VSphere. This detailed analysis revealed several misconfigurations, including improper core/socket layout, incorrect vNIC settings, unsuitable disk controllers, and improper disk formatting for SQL Server data files. Additionally, issues with memory allocation and CPU settings were identified.

XTIVIA envisioned a robust solution by aligning the SQL Server configurations with VMware’s best practices. The architecture was meticulously planned to ensure optimal performance and stability. Key requirements were established, and buy-in was secured from this customer’s stakeholders by demonstrating the potential improvements in performance and reliability. XTIVIA’s innovative approach leveraged advanced virtualization techniques and precise configuration adjustments to address the identified issues.

To enhance SQL Server performance and support high transaction volumes, XTIVIA implemented several key strategies:

vSphere HA Admission Control: Ensured the availability of mission-critical SQL Server workloads by reserving enough resources to power on these workloads, even during planned or unplanned events.

Enabling DRS Functionality: Enabled Dynamic Resource Scheduling (DRS) to balance the load efficiently across the cluster, reducing resource contention and improving performance.

BIOS/UEFI Firmware and Settings: Updated all BIOS/UEFI firmware to the latest versions and applied recommended settings for high-performance environments, including enabling Turbo Boost, Hyper-Threading, and NUMA, and configuring advanced CPU features.

CPU and Memory Configuration: Kept the total number of vCPUs within the physical cores available on the host machine, enabled hyper-threading, avoided memory overcommitment, and set memory reservation equal to the provisioned memory. A critical adjustment was minimizing the number of sockets by provisioning fewer sockets with more cores each. For example, instead of provisioning eight sockets with one core each, we corrected this to one socket with eight cores. This configuration aligns better with the physical NUMA nodes, reducing latency and improving overall performance.

Additionally, we disabled the hot plug CPU feature. Enabling hotplug CPU can turn off virtual NUMA (vNUMA) for the virtual machine, which means the guest OS is unaware of the CPU and memory topology of the ESXi host. This can lead to sub-optimal scheduling decisions by the guest OS, resulting in reduced performance. By disabling hotplug CPU, we ensured that vNUMA remained enabled, allowing the guest OS to use the underlying hardware’s NUMA architecture efficiently, thereby enhancing performance.

We also set the CPU shares to High. This configuration ensured that the SQL Server VMs received a higher CPU resource priority than other VMs on the same host. By assigning more CPU shares, the SQL Server workloads could access more CPU time during periods of contention, improving performance and responsiveness.

Furthermore, we set the latency sensitivity to High. This setting required reserving 100% of the VM’s configured memory and ensured that each virtual CPU was granted exclusive access to a physical core. This configuration minimized latency by optimizing the scheduling of the VM for low-latency workload requirements, which is crucial for high-performance SQL Server operations. The result was a significant reduction in latency and improved transaction processing times.

Network Configuration: Minimized network latency by using VMXNET3 paravirtualized NICs for enhanced network performance. We enabled Receive Side Scaling (RSS) to distribute network processing across multiple CPUs, which helps handle high network traffic more efficiently. We also tuned the ring buffers, transmit descriptor length, and other settings recommended for optimal SQL Server performance. These adjustments ensured the network configuration was optimized to reduce latency and improve overall throughput.

Storage Configuration: Configured virtual disks as eagerzeroedthick to avoid I/O overhead, used VMware Paravirtualized SCSI (PVSCSI) controllers for data and log VMDKs, and employed multiple PVSCSI adapters to distribute I/O load and increase bandwidth.

The solution was deployed into production following rigorous testing and validation. XTIVIA confirmed the results through extensive performance monitoring and user feedback, ensuring the transient failover events and application timeouts were resolved. This customer took ownership of the optimized environment, with XTIVIA providing ongoing support and training to their IT team. This engagement stabilized this customer’s SQL Server environment and enhanced their overall operational efficiency.

The solution’s extensibility and reusability were key considerations throughout the project. The configurations and best practices implemented by XTIVIA can be easily adapted for future expansions or similar environments within this customer. The solution’s long-term capabilities ensure that this customer can maintain high performance and reliability as their infrastructure evolves.

BUSINESS RESULT

The implementation of VMware vSphere best practices resulted in significant performance improvements and operational efficiency for this customer’s SQL Server instances. By adopting the optimized configurations, this customer experienced a substantially reduced latency and faster transaction processing, greatly enhancing the overall user experience. The vSphere HA configuration ensures uninterrupted availability of critical databases, even during host failures, thereby maintaining high availability and disaster recovery capabilities.

This customer measured the results through comprehensive performance monitoring and user feedback. They observed a marked improvement in application responsiveness and a significant decrease in process runtimes. The optimized resource utilization, achieved by reducing resource contention and properly sizing VMs, led to better resource management and reduced operational costs. This optimization also allowed this customer to scale their SQL Server infrastructure more effectively, easily accommodating growing workloads and future expansion.

The changes brought about by the solution positively impacted this customer’s operations, customers, and financial performance. Operational efficiency improved, leading to smoother and more reliable services for their members. Customer satisfaction increased due to the enhanced performance and reduced downtime of critical applications. Financially, the reduced operational costs and improved resource utilization contributed to better budget management and cost savings. Additionally, the scalable infrastructure positioned this customer to handle future growth without significant additional investments.

By leveraging VMware vSphere best practices, this customer successfully optimized its SQL Server performance, ensuring high availability and scalability. This case study demonstrates the importance of a well-designed virtualized environment in achieving enterprise-grade performance for critical database applications. The extensibility and reusability of the solution ensure that this customer can maintain high performance and reliability as their infrastructure evolves, providing long-term benefits and stability.

BY THE NUMBERS
86 years in business (founded in 1938)
437,000 members served
41 customer locations

KEYWORDS
Financial services
Credit union
Customer satisfaction

SOFTWARE
SQL Server 2016-2022

HARDWARE/PLATFORM
Windows / VMWare vSphere

CUSTOMER QUOTE
“Since having XTIVIA take over our SQL Servers, we do not have to worry about our databases anymore.”

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.