Virtualizing SQL Server, some tips.

I just got these considerations about virtualizing SQL Server on VMWare from my Boss and I would like to share them with you.

General comments on problems encountered and why people not virtualizing SQL

  1. Storage ( most people just do not have enough spindles to start with and then when people consolidate they use less spindles not more, error in design and understanding of basic i/o principles )
  2. Queue lengths are a problem so add as many paths to storage as possible.
    Using vmware makes sequential reads look more like random reads due to interleaving of traffic across vm’s
  3. Ensure all dbs ( data, temp, log etc ) are on separate vmdks or preferably rdms, improves i/o queue throughput
  4. Memory ( ensure you set min, max reservations per vm to avoid swapping )
  5. 1 database instance per vmhost ( to allowing you to tweak memory / priorities as required )
  6. Use the latest intel chips, as provide biggest reduction in latencies for vm tasks

Following a thru 6 results in linear performance until the cpu saturates around 90%.

Basically they said VMware is never the bottleneck …

Assuming the rules above are followed you should then consider tweaking the following:

  1. Tweak the o/s first to allow large pages and ensure alignment with storage ( gives +6% improvement )
  2. Turn on sql priority boost in SQL and ensure correct O/S drivers installed in host ( gives +14% when used with 1. )
  3. Turn on static transmit coalescing in ESX networking ( gives +1%, so they advise don’t bother )

They are interesting considerations. Honestly, I know there are some “how to” and some “best practices” when you prepare a SQL environment. Using separate HD for separate files (mdf, ldf), keep system database separate from user database in different disks, split big tables in different location, maintain the indexes and so on … Unfortunately when you reflect this to a Virtual Environment, everything changes.

I didn’t find yet a good documentation about virtualizing SQL but I just tried to build a Clustered environment with Windows 2008 R2 x64 and SQL 08 x64 SP1 and the Quad Core I used for my experiments was not enough …