One of the best sessions to date of VMworld was discussing virtualizing SQL 2008 in vSphere. VMware has written a lengthy whitepaper going over the gory details. In short, you will likely get 86% or better of bare metal performance if you virtualize SQL with vSphere, even for the most demanding workloads. This is in stark contrast with prior versions of ESX which has much more of a performance hit.
However, to achieve that level of performance careful planning and some tweaking of the environment is needed, along with the latest processors from Intel and AMD. Storage performance is key so following your storage array vendor’s best practices for physical SQL servers is still recommended. In addition to storage best practices, other tweaks are recommended as well. These include:
– Modify SQL to use fixed memory allocation
– Create a memory reservation in ESX for the SQL VM
– Use separate vSCSI devices for OS, database and log drives
– Use paravirtualized SCSI adapter for the database and log drives (can’t use for OS)
– Use large 2MB pages for SQL, specially on 64-bit systems
– Create DB/Log vmdk disks with thickeagerzeroed parameter
– Set maximum queue depth on HBA
– Make sure HW assisted virtualization is enabled on the VM
– Enable vmxnet TX coalescing
– Disable SQL server statistics
– Use fiber-mode Windows scheduling
– Use RAW LUNs over NTFS
Performing all of these tweaks can boost SQL performance 20% or more over a plain vanilla installation. In short, vSphere now lets customers virtualize SQL and gain all the benefits of virtualization with very little performance penality. Remember with Server 2008/R2 you can perform hot memory add (enterprise edition), and hot CPU addition (datacenter) as well.
For a good whitepaper on general vSphere performance tweaks, see this link.
