Virtualization of SQL Server these days are becoming more a reality and common practice. Usually if you you mention SQL Server and Virtualization in one sentence people will look at you as if you are a mad man.
With this post I would like to talk about a couple of notes you need to consider when you virtualize SQL Server. When using a hypervisor to consolidate your SQL Servers into a virtualization environment you need to think about the impact on CPU, Memory, Storage and Network performance and management.
Let’s break it a bit down further with some checkpoint.
CPU
- Always ensure that you disable power management for CPUs in the BIOS of the physical servers. You want the full power of your CPU available at all times.
- In your Guest VMs make sure that the Power Management Plan is set to High performance.
- The different levels of CPU cache has a big performance impact. Try to use the latest CPUs that have big level 3 cache. Level 3 cache in the latest Intel CPU are shared among the individual cores. This allow the hypervisor to reuse the same CPU to schedule work among the cores for your SQL Server virtual machine where certain SQL Server data is cache.
- Assign minimal virtual cores to your SQL Server virtual machine and scale up the CPU count as required when workload increases. This allow the hypervisor the reuse the same CPU socket where certain data can remain cache rather than distribute across all available CPU sockets where you will be hit by cache misses.
- Monitor for throttling of the CPU.
- Avoid affinity masking.
- Use CPU-Z to make sure your CPU is performing optimally.
Memory
- From your hypervisor reserve memory to your SQL Server virtual machine. This allow other virtual machines not to consume your memory and allow the Balloon driver not to force your SQL Server virtual machine under memory pressure to release memory.
- Set your SQL Server minimum and maximum memory according to your reserve memory. Don’t assign all available memory you must leave some head room for the Windows Operating System.
Storage
- Still apply the same best practices for SQL Server database files.
- Try and stick to RAID10.
- You can use Pass-Trough disks in your hypervisor. Which give you slightly better performance than virtual disk drives.
- You can use Shared or Dedicated LUNs via your hypervisor. Depended on your business requirements.
- Correctly configure multi-pathing on your SAN.
- Avoid 1GB iSCSI for storage, try going higher.
Network
- Assign dedicated Network Card interfaces via you hypervisor to the SQL Server virtual machine.
- Make sure good practices are followed for network redundancy.
- Avoid multiple virtual machine using the same network interface.
These checkpoints are some of the common practices to follow when setting up SQL Server in a virtualization environment. Virtualization setup comes down to what you can afford and what your business requirements are.
The key areas to successful deployments are:
- Always use the latest version of the vendors hypervisor.
- Follow the vendor’s best practices.
- Always and always monitor the physical environment and guest virtual machines for optimizing the performance.
Microsoft Hyper-V
The Microsoft Hyper-V hypervisor does have its own best practices for deploying SQL Server. One feature that you should try and avoid is dynamic memory. You should always use static memory assignment for SQL Server virtual machines.
Here are some documents to review if you are going to use Microsoft Hyper-V.
- Running SQL Server with Hyper-V Dynamic Memory Best Practices and Considerations
- Running SQL Server 2008 in Hyper-V Best Practices and Performance Recommendations
- High Performance SQL Server Workloads on Hyper-V
VMware vSphere
The VVMware vSphere hypervisor has its own best practices for deploying SQL Server. A very nice feature that vSphere has is memory compression. This feature is where multiple pages in memory that are the same is removed from memory. Only keeping one copy in memory. This is a great benefit for Operating System pages in memory, but no real benefit specifically towards SQL Server.
Here are some documents to review if you are going to use VMware vSphere.
- Availability Guide for Deploying SQL Server on VMware vSphere
- Performance and Scalability of SQL Server on VMware vSphere
- SQL Server on VMware Best Practices Guide
Conclusion
On the end there are allot of guidance from the individual vendors for optimal deployment of SQL Server in an virtualization environment. There are many benefits like cost savings and management in using virtualization. I would really recommend that you try setup a test environment to see the benefits.
Performance is always a concern, but at the rate at which hardware performance are increasing the little bit of performance impact you experience right now with virtualization will be a thing of the past with new hardware. Especially with enterprise SSD disk drives that are coming out at a good price.
One person that I follow that provides brilliant content on SQL Server virtualization is Brent Ozar. He has many articles and videos on virtualization.
If you have any comment or questions about SQL Server virtualization please feel free to contact me.
Cheerio!
