Here are just a few brief common recommendations that I give SQL Server DBAs when virtualizing SQL Server on Hyper-V. The best complete source of best practices, guidance and recommendations are these 3 Microsoft whitepapers, which I reference nearly every day:
- High Performance SQL Server Workload on Hyper-V
- Running SQL Server 2008 in Hyper-V Environment
- Running SQL Server with Hyper-V Dynamic Memory Best Practices and Considerations
Here is my top-10 list which is based on generalities that I have found give DBAs a decent bang-for-the-buck. As with ANY configuration change, your own mileage will vary and make sure to first TEST, TEST, TEST on a non-production system:
- Lock Pages in Memory. In fact, this is quickly becoming a STANDARD recommendation on all SQL Server boxes and VMs that are dedicated as database servers.
- Virtual SCSI-attached virtual disks for all data disks give the best performance for SQL Server.
- Hyper-V does not over-commit memory and instead uses “dynamic memory”. To make best use of this feature, you should set the total of Startup Memory for all the server’s VMs to a value that is lower than host’s physical memory so that all virtual machines can start in the event of an unplanned failover.
- Although you can over-commit CPUs with Hyper-V, try to avoid doing so. Testing has shown that over-committing CPUs has a very heavy burden on overall server performance.
- Make sure that the hardware you are using has CPUs that support SLAT. This makes a HUGE difference in VM performance and you must think about this before ordering your server hardware!
- Also, on that same note, make sure that your server is outfitted with a >=1GB NIC interface because you’ll need it for Live Migration. Also note that Hyper-V will use DMA for VM memory from your NIC card.
- With Hyper-V Live Migration, startup of VMs on another host is much more orderly and better-behaved if you can reduce the the SQL Server buffer pool BEFORE migrating the VM using sp_configure ‘max server memory’.
- If running Hyper-V on a NUMA platform, try disabling NUMA “spanning” to ensure that the VM accesses only local node memory.
- Don’t start-up your SQL Server VMs with over-loaded resources. Benchmark the CPU & RAM needed for each server before virtualizing. You can always ADD resources later (SQL supports hot-add RAM & CPU).
- Because you are adding an abstraction layer (virtualization) to your hardware and will likely begin to exponentially increase the number of SQL Server instances that you monitor and manage within the same or smaller footprint, look at using tools like System Center SCOM and VMM to keep the Hyper-V environment healthy and efficient.