Tips for configuring Microsoft SQL Server in a virtual machine

VMware vSphere ESXi

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

RDBMS performance characteristics:

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.

Use uni-processor virtual machines to begin with, upgrading 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 you create 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 your read/write ratio to determine if your database is write intensive.
After your 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 you 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:

For additional information, see Virtualizing Enterprise Applications: SQL Server.
Allocate adequate CPU resources.
Layout disks for maximum I/O throughput.

For translated versions of this article, see: