General Guidance for SQL Server on Virtualisation

I have been on about four engagements in a row this year where we are looking at SQL performance on VMWare or Hyper-V.

Here is a list of common things that you can do with virtualisation that may adversely affect SQL performance. Most of them also apply to physical environments, for example if you are consolidating SQL onto multiple instances.

   * Using a large Shared disk group for all virtual workloads
   * Mounting the VHD to VMWare disk on a server file system (instead of pass though disks)
   * Using a large disk pool when only one controller can own the disk group (some SAN’s are limited in this way and some are not)
   * Overcommiting CPU
   * Overcommiting Memory
   * Not using 64k block size and allocation unit size
   * Not using Volume alignment (on guest and host)
   * Using dynamic disks (much better in Hyper-V R2, but still not generally recommended)
   * Not ensuring Logs are on dedicated spindles
   * Not using multiple HBA channels on larger workloads
   * Sharing a switch between data, network and CSV
   * Not using CPU affinity (some virtualisation platforms support affinity)
   * Not using an “enlightened” operating system (Hyper-V)
   * Running multiple VM’s on a single host slightly decreases throughout, but this is kinda the point of virtualisation so hard to avoid.
   * Running lots of SQL Servers on one host and having too few HBA cards or a low queue depth
   * Running 32 bit SQL Server guest on workloads that need lot of memory.
   * Not pre-sizing TempDB
   * not planning for database growth events

The top one item (use of a shared disk group) is a very common configuration for disks, especially when using the clustered shared disk volumes. But we know they will adversely affect performance, so what to do ? ban these configurations?

Read more: Bob Duffy's Blobby Blog

Posted via email from jasper22's posterous