Solid State Drive Performance Hands-On: Part 1

<div><p align="center"><em>(Punch it, Hurb <br/>Yo, I don't think we should talk about this <br/>Come on, why not? <br/>People might misunderstand what we're tryin' to say, you know? <br/>No, but that's a part of life)</em></p> <p align="center"><em>Let's talk about S-S-D <br/>Let's talk about you and me <br/>Let's talk about all the good things <br/>And the bad things that may be <br/>Let's talk megabytes\secs <br/>Let's talk megabytes\secs</em></p> <p>OK, I'm no Salt-N-Pepa, but now that I've got your attention (maybe even made you laugh?) I wnt to spend some time talking about Solid State Drives (aka SSDs). This is the first in a series of posts about the state of SSDs as they relate to the SQL Server landscape today. Here's what I'm going to cover:</p> <blockquote> <p>Part 1: Introduction and SSD Fundamentals <br/>Part 2: Fusion-IO ioDrive <br/>Part 3: OCZ Z-Drive <br/>Part 4: SSD Performance Shootout <br/>Part 5: Are SSDs Right For You?</p> </blockquote> <p>So let's get started!</p> <p><strong>Introduction <br/></strong>Traditional hard drives (HDDs) have long been one of the biggest (if not the biggest) bottleneck in database performance. At the 2009 PASS Summit Dr. David DeWitt asserted during his keynote that disk performance has gotten <em>slower </em>in relative comparison to the improvements made in CPU and memory over the last 30 years. So much slower, in fact, that some of the smartest people on the planet are spending their time trying to figure out how to store data differently to get around the disk bottleneck. He mentioned that SSDs will really change our lives and provide "the only real hope" for fixing the disk performance issues.</p> <p>Unfortunately one of the biggest problems with SSDs is that for years they've been extraordinarily expensive - so expensive that only the likes of the military have been able to afford them. The good news is that they're finally coming down to a price point that even SMBs can swallow. Today you can purchase SSDs in new laptops and the storage technology behind SSDs exists in mobile phones, MP3 players, and USB thumb drives. </p> <p>But are SSDs a good match for SQL Server? Before I can answer that question, I'll start at the very beginning with a look at how SSDs work.</p> <p><strong>Form Factor <br/></strong>SSDs come in one of four form factors: SATA, PCI Express, SAS, and Fibre Channel. Generally you'll find SATA drives in notebooks and desktops ($), PCI Express drives in servers ($$), and SAS & Fibre Channel drives in SANS ($$$). PCI Express based drives are usually not bootable and require some sort of driver or software install before they can be used by the OS. Also, PCI Express based drives contain their own controller card; as such if you want to put these into a RAID solution you're limited to a software based RAID.</p> <p><strong>Storage - SLC vs. MLC</strong> <br/>Unlike HDDs which have a lot of moving parts, SSDs are all the same at heart: a bunch of <a href="http://en.wikipedia.org/wiki/Flash_memory#NAND_flash" target="_blank">NAND flash</a> chips and no mechanical components. Data on flash memory is stored in individual memory cells made of <a href="http://en.wikipedia.org/wiki/Floating-gate_transistors" target="_blank">floating gate transistors</a>. Today's memory cells are capable of storing either two or four states. Cells that store two states are called Single Level Cell (SLC) and hold one bit (e.g. 0 or 1). Cells that store four states are called Multi Level Cell (MLC) and hold two bits. Because SLC stores less data per cell than MLC it not only costs more to manufacture but requires a larger physical footprint to store the same amount of memory as its MLC counterpart so consequently you pay (a lot) more for an SLC based SSD.</p> <p><strong>Write Endurance <br/></strong>NAND flash has a limited number of times that it can be written to before it fails. SLC based flash have a lifetime of approximately 100,000 writes per cell and MLC's lifetime is around 3,000-10,000 writes per cell. A technique called <a href="http://en.wikipedia.org/wiki/Wear_leveling" target="_blank">wear levelling</a> is used to avoid burning out a cell by ensuring that writes take place evenly across all cells on the SSD. A simple example is a least recently used (LRU) queue, but every manufacturer has their own wear levelling "secret sauce" to maximize the life of their drive.</p> <p>Eventually cells do fail for one reason or another, so to counter this manufacturers add extra capacity to their drives that isn't available to the OS. For example, if you buy a 120 GB drive there may actually be 144 GB of capacity (e.g. 20% extra) on the board. This is known as "over provisioning".</p> <p><strong>Space Management</strong> <br/>When data is deleted from an SSD it isn't really deleted - it's just marked as not in use…in order to reuse the space safely the SSD needs to be told it's OK to overwrite. This is referred to as a TRIM command, and prior to Windows 7 & Windows 2008 R2 implementation of TRIM was left to each SSD manufacturer (Win7 & 2008 R2 now support this as part of the OS). Some implementations required massive amounts of RAM, and in some cases very undesirable things could happen to performance (or to the drive itself) if there wasn't enough RAM available. TRIM is a background process and not something that you need to be concerned about running manually.</p> <p><strong>Performance</strong> <br/>Now that you know the fundamentals of how SSDs work, let's talk performance. In part 2 of this series I will take a look at how the ioDrive, a PCI Express based solution from Fusion IO, handles SQL Server activity.</p> <p><em></em></p> <p><em>Note: This post is part of T-SQL Tuesday #004 which is focused on IO this week. Follow the link to check out the other IO related posts from some great SQL bloggers!</em></p> </div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Performance