SQL Server Performance Tuning and Optimization on VMware

Introduction
Physical Host Configuration
ESXi Configuration
VM Configuration
Windows Configuration
SQL Server Instance Configuration
Database Maintenance and Configuration
Queue Depth Settings
Conclusion

Introduction

Welcome to our quick reference guide for optimizing SQL Server performance on VMware, based on recommendations from the "Architecting Microsoft SQL Server on VMware vSphere." Designed specifically for SQL Server DBAs and System Administrators, this post focuses on impactful configurations and settings to ensure smooth operation within VMware environments.

Physical Host Configuration

Given SQL Server's unique workload patterns, it's typically advantageous to allocate dedicated resources for its operations. While a dedicated cluster offers optimal performance and resource management, dedicated hosts within the VMware environment can also serve to isolate SQL Server VMs. By ensuring these hosts are exclusively reserved for SQL Server workloads and isolated from other VMs, administrators mitigate the risk of resource contention and maintain consistent performance levels. This approach safeguards SQL Server's stability and performance by providing uninterrupted access to essential computing resources.
In summary, while a dedicated cluster is ideal, dedicating hosts within the VMware environment ensures SQL Server VMs receive the necessary resources for optimal performance. Strict isolation from other workloads minimizes resource conflicts and maintains consistent performance levels, ultimately enhancing SQL Server's reliability and scalability within the VMware infrastructure.

• Enable Hyper-threading
  
• Enable Turbo Boost
  
• NUMA Enabled
  
• Enable VT-x/VT-d
  
• Power Management set to OS Controlled
  
• Disable all processor C-states (C1E halt state)
  

ESXi Configuration

• Set Power Management to High Performance to optimize system performance.
  
• Exercise caution with DRS, as it may automatically migrate VMs based on resource usage. Collaborate closely with VMware administrators to ensure they are aware of your workload patterns and avoid unnecessary VMotion of your SQL Server workloads, especially if they begin experiencing increased resource demands.
  

This proactive approach helps maintain stability and consistent performance for SQL Server instances running on VMware infrastructure.

VM Configuration

Create a Virtual Machine Template with predefined configurations as following. When necessary, use this template to provision virtual machines, allowing you to adjust CPU, memory, and disk settings according to the workload requirements. This approach streamlines the process of creating new virtual machines while ensuring consistency and alignment with workload demands.

• Memory Configuration
  • Avoid over-allocating memory, exceeding host capacity.
  • Consider setting memory reservations for critical workloads to prevent ballooning.
  
  
• vCPU Configuration
  • VMware adopts a "deploy and measure" approach, initially surpassing the available number of physical CPUs.
  • Best practices are as follows:
    • For high-performance systems - ensure that the total vCPU allocation does not exceed the total number of physical CPUs available in the cluster.
    • For lower-tier systems - it is permissible to over-allocate vCPUs to physical CPUs, but careful monitoring of workload impact is essential.
  • The "User CPU ready" and "Co-Stop" values are guidelines used to assess the performance of SQL Server databases running on virtual machines (VMs).
    • User CPU ready refers to the amount of time a virtual machine's CPU is ready to execute instructions but is waiting to be scheduled on a physical CPU. Ideally, this value should be low (5%>), indicating that the virtual machine has quick access to CPU resources.
    • Co-Stop represents the time a virtual machine is waiting for multiple virtual CPUs to be scheduled simultaneously on physical CPUs. A high Co-Stop value suggests contention for CPU resources among multiple virtual machines on the same physical host, potentially impacting SQL Server performance.
      Monitoring and managing these values help ensure optimal performance of SQL Server databases on VMs by identifying and addressing CPU resource contention issues.
    
    
• Cores per socket For VMware 6.0 and above, the platform automatically configures the appropriate NUMA (Non-Uniform Memory Access) setup. However, if necessary, you can customize this configuration using advanced settings.
  • In VMware 6.5 and later versions, the "Cores Per Socket" parameter no longer influences the vNUMA (Virtual Non-Uniform Memory Access) topology when creating a virtual machine.
  • A general rule to follow is to align your virtual machine configurations with the underlying hardware topology. For instance, if your physical machines have 6 cores each, avoid creating virtual machines with configurations like 4 or 8 cores.
  • It's important to note that SQL Server Standard edition is limited to using either 4 sockets or 24 cores, whichever is lower, and it does not inherently support NUMA. You can enforce this configuration to ensure compatibility and optimal performance.
  • After deploying, verify the NUMA topology within SQL Server by checking the Server Properties.
  
  
• CPU Hot Plug - Disabled
  Disabling CPU Hot Plug is recommended as enabling it can result in a significance performance impact. Reports suggest that enabling CPU Hot Plug may incur up to a 30% increase in CPU costs, as it disables virtual Non-Uniform Memory Access (vNUMA).
  
  
• Disk Configuration
  • Attach at least 4 PVSCSI disk adapters to the VM hosting your SQL Server database
  • Create a 95GB virtual disk attached to a PVSCSI adapter for the operating system (OS).
  • When deciding on the type of disk to use (VMDK, VMware Virtual Volume, or Raw Device Mapping), consult with your storage administrators and vendors to understand the capabilities of your environment.
  • Create and attach four additional 10GB volumes (Adjust the volume size based on the size of your environment).
    • Dedicate disks to different types of data, including database files, transaction log files, system databases, and TempDB. If conducting backups to a local disk, add an additional dedicated disk for that purpose.
    • Attach one disk to each PVSCSI adapter. You will need to double up one virtual disk on the PVSCSI adapter with the OS virtual disk. However, the OS should not generate excessive IO during normal operations, making this insignificant.
    • Here's an example layout for virtual disks to PVSCSI adapters for OLTP workloads.

  
  PVSCI Adapter	Virtual Disk Workload 
         0	       Operating System, System Databases
         1	       Data files - mdf/ndf
         2	       Transaction log files - ldf
         3	       TempDB - both data and log
  

  • If additional disks are required, evenly distribute the workload across the PVSCSI adapters according to the IO demands of the database files assigned to those disks.
  • Continuously monitor file latency, IOs, and queuing over time to assess disk contention. If necessary, rebalance the workload among the four PVSCSI adapters to optimize performance.
  • During provisioning, adjust the disk size to meet the workload requirements of the VM.
  
  

Windows Configuration

• Power Plan: Set to "High Performance."
• Page File Size: Configure as a fixed 8GB allocation.
• Windows Updates: Ensure all updates are applied before installing SQL Server.
• Instant File Initialization: Configure the Local Security Policy to enable "Perform Volume Management" for the SQL Server Service account.
• Lock Pages in Memory: Configure the Local Security Policy to enable "Lock Pages In Memory" for the SQL Server Service account.
• Volume Format: Format all SQL Server volumes with 64KB NTFS Allocation Units.
• Block Alignment: While partitions have been volume aligned by default since Windows 2008, it's still advisable to verify proper configuration.
• SQL Server Folder Structure: Create the following folder structure for SQL Server:

  E:\DATA 
  F:\LOGS 
  G:\SYSTEM 
  H:\TempDB 
  

  Additional volumes can be attached as mount points if necessary.
• Anti-Virus: Ensure that your antivirus system excludes SQL server engine processes, mdf, ndf, ldf, sdf, and bak files.

SQL Server Instance Configuration

• Maximum Memory: Tier 1 workloads should have both minimum and maximum values defined.
• MAXDOP(Max Degree of Parallelism): Set to be less than or equal to the maximum number of cores in a NUMA node or 8, whichever is lower.
• CTFP (Cost Threshold for Parallelism): Initially set to 50, then adjust as needed based on workload performance.
• Optimize for Ad-hoc Workloads: Set to True
• Processors: Enable the following two optoins -
  Automatically set processor affinity mask for all processors
  Automatically set I/O affinity mask for all processors
  

Database Maintenance and Configuration

• Auto Create Statistics: Set to True
• Auto Update Statistics: Set to True
• Target Recovery Time (Seconds): Set to 60
• Backups: It's essential to maintain backups. Try to distribute them evenly across your backup window. You can employ a layered approach by combining array-based snapshots with SQL Server native backups.
• Index and Statistics Maintenance: Consider staggering maintenance jobs and utilize an intelligent maintenance framework like Ola Hallengren’s
• Data File Layout: For large and demanding databases, distribute data into file groups and data files aligned with the disk topology. Add extra disks as needed for the workload.
• TempDB: Create one TempDB file per core, up to 8 cores.
• Monitoring: Develop a SQL Server-specific monitoring strategy to capture workload and system metrics.
• Enable Query Store: Use Query Store as a flight data recorder for identifying performance issues.

Conclusion

By following these guidelines, SQL Server DBAs can effectively configure and optimize their SQL Server deployments on VMware, ensuring maximum performance and reliability. For detailed explanations and infrastructure setup, refer to the "Architecting Microsoft SQL Server on VMware vSphere" guide and consult with VMware administrators and experts.

Related content

• SQL Server Session Logging with sp_session_logger
• Logging SQL Server Query Performance with sp_query_logger
• Searching the SQL Server query plan cache
• Identifying Top Wait Events in SQL Server
• How to check performance counters in SQL Server
• IO Latency in SQL Server
• SQL Server TempDB: Best Practices for Optimal Performance

Rate Your Experience

: 90 : 1