The VMware Knowledge Base provides support solutions, error messages and troubleshooting guides
Search the VMware Knowledge Base (KB)
View by Article ID
Tips for configuring Microsoft SQL Server in a virtual machine (1002951)
Details
This article provides a quick list of points to consider when configuring Microsoft SQL Server in a virtual machine.
Solution
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 effecting 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
Allocate adequate CPU resources:
Database performance suffers if CPU resources are constrained
During levels of high database activity, run the DBCC SQLPERF(UMSSTATS) command against your database
This returns scheduler statistics for each CPU. If the num runnable metric, that is the Scheduler Queue Length is greater than 2, there may be CPU constraints, causing SQL Server threads to queue.
Consider increasing minimum and maximum CPU resource allocations to reduce this queue length.
Use uni-processor virtual machines. 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.
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
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
