General guidelines for deploying SQL on VMware vSphere

CPU:

  • Start with a thorough understanding of your workload, you can use VMware Capacity Planner to determine workloads
  • If workloads cannot be determined, start with 1 vCPU, single vCPU VM can support high transaction throughput
  • Account for virtualization overheads (8%-15%, depending on the workload)
  • In Windows Server 2003 guests, when using single-processor virtual machines, configure with a UP HAL (hardware abstraction layer) or kernel. Multi-processor virtual machines must be configured with an SMP HAL/kernel. Windows Server 2008 will automatically select the HAL appropriate for the underlying hardware
  • Avoid CPU overcommitment, otherwise Reserve the full capacity
  • Install/Update latest vmware tools
  • Ensure CPU Compatibility is met for vMotion

Memory:

  • Start with a thorough understanding of your workload, you can use VMware Capacity Planner to determine workloads
  • Increase the database buffer cache to reduce or avoid disk I/O and thus improve SQL Server performance
  • Avoid Memory overcommitment, otherwise reservations can be applied to avoid Ballooning and Swapping
  • If you set the SQL Server lock pages in memory parameter, be sure to set the virtual machine’s reservations to match the amount of memory you set in the virtual machine configuration
  • Use of large pages will help in improving performance

Storage:

There are no concrete recommendations for using VMFS or RDM in SQL Server deployments, both have their advantages and disadvantages. Fibre Channel may provide maximum I/O throughput, but iSCSI and NFS may offer a better price-performance ratio VMware test results show that aligning VMFS partitions to 64KB track boundaries results in reduced latency and increased throughput

It is considered a best practice to:

  • RDM is required when using third-party clustering software, storage based backups to disk or use of third party storage Management software
  • Guest OS can be installed on VMFS and SQL DB and Logs can be on RDM’s
  • Maintain a 1:1 mapping between the number of virtual machines and LUNs to avoid any disk I/O contention
  • It’s recommended to have VMDK’s as eagerzeroedthick
  • Aligning VMFS partitions to 64KB track boundaries results in reduced latency and increased throughput Create VMFS partitions from within vCenter. They are aligned by default
  • Setup a minimum of four paths from an ESX/ESXi host to storage Array, this means that each host requires at least 2 HBA ports

Network:

  • Use NIC teaming and segregate network traffic using VLANs
  • Use the VMXNET3 network adapter for optimal performance. The Enhanced VMXNET3 driver also supports jumbo frames and TSO for better network performance
  • Network communications between co-located virtual machines usually outperforms physical 1Gbps network speed so, if possible, place the various virtual machines that make up an application stack on the same ESXi host

Performance Counters of Interest to SQL Administrators

Subsystem esxtop Counters vCenter Counter
CPU %RDY%USED Ready (milliseconds in a 20,000 ms window) Usage
Memory %ACTVSWW/sSWR/s ActiveSwapin RateSwapout Rate
Storage ACTVDAVG/cmdKAVG/cmd CommandsdeviceWriteLatency & deviceReadLatency kernelWriteLatency & kernelReadLatency
Network MbRX/sMbTX/s packetsRxpacketsTx

*The above content is provided with an Assumption that the VMware environment is vSphere 4.x and later

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s