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

From a storage perspective, this issue could arise:

• If vmdks are provisioned as thin and the backing LUN is also provisioned as thin then we have thin on thin. In this scenario it's easy for the vmdks to become over provisioned resulting in the backing LUN to fill up and go offline due to no free space if properly not monitoring storage space.

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.