Tips for configuring Microsoft SQL Server in a virtual machine

Products

• VMware vSphere ESXi

Issue/Introduction

This article provides a list of points to consider when configuring Microsoft SQL Server in a virtual machine.

Cause

RDBMS performance characteristics:

• Traditionally CPU and Disk I/O intensive
• Require adequate amounts of CPU power to prevent SQL scheduler thread queuing
• Require fast I/O throughput to write logs without affecting query performance

From a performance perspective, these issues arise:

• CPU contention with other processes and virtual machines
• Disk I/O contention when writing logs or dealing with write-intensive database activity

Resolution

Use uni-processor virtual machines to begin with and upgrade to VSMP virtual machines only if necessary.

• It is easy to move from a UP HAL to a SMP HAL, but not vice-versa.
• Upgrading from UP to SMP mode requires a compulsive HAL change which can result in HLT related issues after the upgrade when using Win2k virtual machines.
• Re-installation is required only when moving between ACPI and non-ACPI configurations. Virtual machines with two VCPUs require two PCPUs to be available to service a request. Systems with few PCPUs or many VSMP.
• Virtual machines may see poor performance.

Adjusting CPU resources from uni-processor to SNP when needed:

• Database performance suffers if CPU resources are constrained.
• Consider increasing minimum and maximum CPU resource allocations to reduce this queue length.

Separate data and logs on different physical disks:

• A standard RDBMS best practice is to place SQL data and logs onto separate physical disks. This ensures that I/O intensive logging does not interfere with data queries.
• In a virtual machine, create one (or more) .vmdk(s) for data and another for logs. Pre-allocate disk space when creating the virtual disks.
  
  Note: An auto-growing .vmdk sacrifices some performance for the added flexibility.
  
  
• Put the .vmdk on a different LUN to spread activity across multiple spindles.
  
  This allows sequential writes to occur as fast as possible, helping to boost I/O performance.

Consider RAID 10 for write intensive databases:

• RAID 10 is more efficient than RAID 5 for writes, but requires more disks.
• Calculate the read/write ratio to determine if the database is write intensive.
• After the database has been running for a few days, run Windows Task Manager and view the total number of I/O Read Bytes and I/O Write Bytes for the sqlservr.exe process.
  
  This shows the ratio of SQL Server reads to writes since the SQL Server service was last restarted.

Disk provisioning and alignment:

• Configure the vmdks as eagerzeroedthick to avoid overhead I/O for space provisioning.
• Systems prior to Server 2008 may need the partitions aligned for additional performance. See manufacturer for details on this process.

Disable screen savers and unused devices:

• Screen savers and devices like floppy drives, CD-ROM drives, serial ports, etc. consume resources, even when they are not in use.

Summary:

• Allocate adequate CPU resources.
• Layout disks for maximum I/O throughput.