In my recent “Why Is My Virtual SQL Server So Slow?” webcast, we got a lot of great questions in chat. Here’s some of the highlights:
Question: “Why would I virtualize a 4-core server and turn it into a 2-core VM? Doesn’t performance go down?”
If you’ve got an existing physical box, and your only goal is to increase performance, then I probably wouldn’t virtualize. However, it’s pretty rare that a company is only looking for more speed – usually they’re looking for lower costs, less heat in the datacenter, less power consumption, and so forth.
Question: “Is there any way to collect VMWare counters from within TSQL to capture those values for those specific counters and capture into a SQL table? I can already capture the SQL counters, but cannot find a way to collect the VMware counters?”
Not that I’m aware of, but I have to confess that I don’t look too hard for that kind of thing. I want more than just the VMware counters – I want counters like Physical Disk Avg Sec/Read, and those aren’t available easily inside T-SQL either. Instead, I use Perfmon to capture it as explained in my Perfmon tutorial post, or a third party SQL Server performance monitoring utility.
Question: “Hi Brent. Great presentation! How to deal with the arrogant SA who refuses to believe there is any impact to SQL from virtualizing? This guy even said that Microsoft now suggests that you virtualize SQL Server!”
Yes, take HP’s Database Consolidation Appliance developed in partnership with Microsoft. Virtualization is a key part of that solution. Yes, depending on how you architect it, you could take a huge performance hit – but I can say the same thing about physical boxes. If I saddle a physical box with just 3 drives in a RAID 5 configuration, and I put the OS, SQL binaries, data files, log files, and TempDB on those drives, there’s often a big performance impact. I’ve seen plenty of virtual servers suffering from similar design bottlenecks, like 30 virtual servers sharing 15 hard drives in a RAID 5 config. The key is to design and provision appropriately.
Question: “Regarding Virtualization and SAN… is there a recommendation for setting block size on SQL Server 2008?”
There’s a few related settings here including NTFS allocation unit size, RAID stripe size, and partition offset. Check with your SAN vendor’s documentation to see what’s right for you. In most cases for OLTP databases, you’re in decent shape with 64K NTFS allocation unit size and RAID stripe size, and a 1mb partition offset.
Question: “I’m using Brand X storage. Is that a problem?”
There was a lot of brand bashing in chat about a particular SAN vendor, and I’m disappointed by that. I’ve seen crappy performance out of every single vendor out there – but it’s rarely due to the storage itself. Instead, most of the time it’s us meatbags who make bad implementation decisions and cripple storage performance. It’s a poor musician that blames his instrument. (Unless your instrument is a software-based SAN that runs on commodity servers using crappy storage, in which case, it’s a tin can banjo, and you should blame it.)
Question: “Brent, would you ever recommend running a production SQL database on a VM?”
Yes. I’ve been doing it since 2006, and I’ve got clients doing it.
Question: “Are virtual servers more secure than physical ones?”
I don’t think so, but I don’t think any kind of servers are inherently secure.
Question: “Can SQL perform well having thin provisioning on the SAN?”
If the primary bottleneck isn’t storage waits due to expanding the drives, yes. For example, if you’ve got a 100GB database that’s fairly stable at 100GB, but you’ve thin provisioned the storage to grow to 500GB if necessary, then thin provisioning wouldn’t be an impact.
Question: “We are running a SQL cluster in a VMware environment. Any thoughts to this?”
Generally, I don’t like doing this. When a business wants to do it, I ask if the databases involved are truly mission-critical. Do they absolutely, positively, have to be up all the time, with as little downtime as possible? If so, let’s put them on a physical cluster. Troubleshooting clusters is hairy enough – there’s already enough finger-pointing between the OS, app, and hardware guys. Introducing virtualization makes troubleshooting just that much more complex, and that’s an area where I want less complexity. If, on the other hand, the database isn’t truly mission-critical, then I’d ask why we’re clustering it.
Question: “Why would you NOT want a dedicated server for your database? Does anybody think they have CPU or disk cycles lying around that they can’t use and want to share?”
So are you saying you’re running all of your databases on 8-socket monsters with solid state drives, and you’ve got 100% CPU use? In that case, you shouldn’t virtualize. Otherwise, you’re probably not using the latest and greatest technology on every SQL Server to begin with, so you’ve already got some headroom. With SQL Server 2012 Enterprise Edition licensing coming in at $7,000 per core, I find that most businesses who aren’t running 100% CPU use are starting to ask tough questions to their DBAs.
I’ve got a 3-hour video training session on how to manage SQL Server in VMware. Check it out now!