I went to a SQL Server lecture tonight on Virtual Machines and SQL Server by Brent Ozar. It wasn’t what I expected. The lecture was great, as have been all the ones in this lecture series, but the advice was decidedly more mixed than I thought it’d be.
The takeaway message was that Virtual Machines don’t make SQL Server better, they make it cheaper. That’s likely a quote from some point in the lecture as that concept was repeated many times. He highlighted some of the benefits:
- Easier to move resources
- Easier to target databases to the appropriate environments
- Easier to group databases for maintenance
- Virtual machines have their own version of clustering/replication that allows them to be up more reliably
- You can have more environments without having to worry about cross-effects
However, he made it very clear that if you have SQL Server on virtual machines and your shop is large enough that someone else is in charge of the virtual machines, you’re going to constantly be fighting for resources or at the very least are going to have to monitor them vigilantly to ensure SQL Server isn’t being starved of what it needs.
- Use Perfmon to monitor various aspects of your memory allocation
- Also use it to monitor various aspects of your CPU allocation
- Ensure you have a reasonable floor established for memory and CPU
- Try to make sure you’re going to be using the same CPU set for your processes
- Have the settings for the “balloon driver” set in such a way that it won’t steal resources from SQL Server
- Monitor manually where your files are sitting
The Baloon Driver is an odd little feature of virtual machine management. All it does is constantly pretend it needs memory and ask for it from all available resources. This is to make sure no program is hogging all the RAM, but SQL Server needs to have a lot of memory allocated to it and needs it to be at a fairly high constant level. If the balloon driver is taking those resources away every 30 seconds, SQL Server performance will suffer.
I also found out the reason for something I’d previously considered inexplicable: Why newer computers have CPUs that cycle down to lower speeds. It is to save power and therefore money. This too can have a bad effect on SQL Server performance. In fact, it can have a horrible effect on any program’s performance. This is made worse in a virtual machine environment because you have less of an idea of when the machine will be cycled and the CPUs will be restored to high performance.
We were shown a manufacturer’s description of one of the known problems. The CPUs can cycle down to 800 Mhz and when they do, they stay there. Forever. Nothing will bring them back up except for cycling the machine. Rebooting may fix the problem. I’m not a hardware guy so I don’t know what “cycling the machine” entails, but if it is more involved than simply rebooting, it can’t be good. Rebooting on a production machine is bad enough!
He showed us some tools to get to monitor performance and apparently you really need to get them because otherwise the Virtual Machine Admin can hide all the performance metrics from observation by normal monitors like Task Manager. The tool he suggested was CPU-Z from CPUID.com. I’ll likely check it out, but I don’t know that I’ll get to try it out as we don’t as yet have any virtual machines.
One other piece of advice he gave us makes me question whether or not virtual machines will actually be useful to us. He said that for databases over 100 Gig, they’re of limited utility and that we should keep them on physical hardware unless we absolutely have to move them.