Brent Ozar and Kevin Kline on Solid State Disk Storage

Everyone needs storage, but it can be difficult to determine what type of storage you actually need for your SQL Server environment. It can be especially hard to decide whether to upgrade to solid state disk (SSD) storage. At TechEd 2010, I spoke with Brent Ozar, a SQL Server DBA expert for Quest Software, and Kevin Kline, strategy manager for SQL Server at Quest Software, about SSDs and the business cases for using SSDs with SQL Server. (To see first three parts of my interview with Kevin and Brent, check out the Database Administration blog.)

Megan Keller: We’re seeing huge advances in storage. Do you expect this to grow throughout the third-party vendor community, and are Microsoft and the SQL Server community ready for advanced storage hardware?

Kevin Kline: Yes, and sort of. Is it going to grow; is it going to proliferate? Absolutely. The price points are going to come down, the adoption is going to go up. There are certainly a lot of people who it’s making a lot of difference to. Is Microsoft ready for it? What happens is when you have the I/O pipeline constructed in such a way that it’s still thinking about an armature moving across a spinning platter. Memory has a very different kind of channel than disk I/O does, so if we can go to SSD, which is essentially memory on flash drives, we can really change the way we do this. We don’t know when, but it’s definitely in the offing.

Brent Ozar: I’ve seen scenarios where I’ve already recommended to customers that here’s how you can better utilize solid state and do it in small bursts. You don’t have to spend 100 grand to go with the benefit of SSDs, you can put them in very targeted uses—tempdb is one, or if you’ve got reporting tables that you know you can regenerate from your source data very quickly. The problem is that it takes a lot of DBA man power and work to set that up correctly right now. When Windows 7 came out, I think it was even Vista, you plug in a flash drive and it was ready to use. That’s nowhere near the case in SQL Server. You still have to architect your solution and say, “Where do I want to land this data on flash drives; solid state drives?” I would hope that we get to the point where SQL Server is smarter and I can say, “Use this fast storage over here when it’s appropriate as a second level cache,” but we are nowhere near that level of ease of use. SAN vendors are at least doing tiered storage, and I love that for what it is, but because changing your SAN is such a hard and expensive infrastructure decision, it’s not like that affects SQL Server DBAs today.

Kline: Why don’t you talk a little bit about your blog postings about Fusion-io and the findings you had there.

Ozar: I did some reviews of a product called Fusion-io. There are several companies that offer solid state drives that go into the PCI express bus of a server instead of getting hung off of the serial-attached SCSI. If your data can fit on one of those, you can’t get faster performance without spending at least six digits of money and hiring a dedication SAN administrator. It really is one of those where you can just drop it in and get a dramatic improvement as long as the thing completely fits on this solid state drive. But if not, you have to make some architectural decisions about where you’re going to lay which tables down on the solid state drives versus on your SAN. And it’s not quite plug, play, and take off, yet.

Keller: SSDs are still relatively expensive, but companies and decision makers have to be looking at them as a cost savings when it comes to energy.

Kline: Oh, it is.

Keller: It’s going to come down to whether people can justify it to their decision makers.

Ozar: Even if you throw aside the energy [savings]. I was on one particular project where I said, “Look, you can go buy this right now, spend whatever grand it is, or let’s talk about how many man weeks it’s going to take to make your existing storage that fast in terms of stored procedure changes, index changes, and that’s going to involve testing. Whereas if I drop in this drive right now there’s no testing; immediately you’re taking off.” There’s a real ROI there, it’s just you have to be in a pain point for your performance before you think about it.

Kline: There are a couple great reference cases for SSD and SQL Server. Of course, I think the best-known one is probably MySpace. They use a huge amount of it, and Fusion-io is very happy to tout them. There’s another really neat use case from a company called, gosh I don’t remember their name, but they run an MMORG [Massively multiplayer online role-playing game]. Their MMORG is called EVE Online, and it is to World of Warcraft what Star Trek is to the Trekkie fans, so it’s all sci-fi oriented. It’s all SQL Server and they switched to not PCI but a standard VDI, virtual hard disk, SSD, and they got like a 40-fold increase in performance. The two kinds of SSDs that you can utilize with your SQL Server database are either a virtual hard disk, essentially just a hard disk, an F drive or a G drive, and like iSCSI or a SAN, fiber channel. Or you can have the ones that are PCI-based, and there are advantages to that definitely because you’re right on the BUS, so you don’t have to go out through a separate channel. But there’s also advantages to the other side of the coin, which is it’s basically just a hard drive. You don’t have to worry about threading or anything like that. But I think that we’re going to see both of those applications.

People will have good reasons to do one or the other in greater number in the future, particularly if you’re in a very performance-oriented situation. Some of our customers use it, and I’m probably not at liberty to say their names, but most of them have very heavy website utilization. So I’ll just make up an example here, but let’s say you make the world’s most popular chocolate Easter bunny. You’re going to have most of your sales in the few weeks before Easter rolls around, so in a situation like that, it really makes a big difference to have a performance profile where you can hit huge numbers part of the year, and then the rest of the year you don’t have to.