Making Sense of PCIe SSD Use with SQL Server

,

When Fusion IO burst onto the scene in 2007, offering a PCIe card that was said to be able to achieve 100,000 IOPS, the IT world went wild.  While Fusion IO certainly is a pioneer in the PCIe SSD space, this technology has come a long way in its short life, and they aren’t the only player anymore.  Choosing the right PCIe SSD for your SQL Server can be a daunting task, so lets dive in and distill this technology.

SLC? MLC? v-NAND? NVMe?  Uh, what?

First things first – here are definitions of some of the common acronyms in this technology:

SLC (Single Level Cell) – Stores a single bit of data per cell.  This means faster writes, more durability, and more expensive.  Think ‘write-optimized’ for this breed.

MLC (Multi Level Cell) – Stores multiple bits of data per cell.  This means a denser card which is cheaper to produce, but also wears out faster than SLC when written to.  If optimizing read heavy workloads are your goal, these cards will typically achieve the same read speed as their SLC counterpart, for much cheaper.

NAND – NAND is a type of flash memory that is composed of an array of SLC or MLC cells, laid out in a horizontal fashion.  This is the type of memory you find in most consumer and commercial flash devices (SD cards, USB flash drives, SSD hard drives, etc).

v-NAND (Vertical NAND) – Instead of stacking the cell arrays horizontally, manufacturers have started to figure out how to stack them vertically for more capacity and speed.  Samsung is one of the major manufacturers doing this, and they have a good overview here (along with a video that felt more like a Star Wars trailer than a flash memory tutorial):

NVMe – NVMe is the standardization of the driver set for local flash storage.  This is one of the main differences between the Fusion IO PCIe SSDs and the PCIe SSDs offered by companies like Intel and Samsung.  Fusion IO relies on a proprietary driver set that you have to install.  SSDs that utilize NVMe are more ‘plug and play’ – install the card into an open PCIe slot, and away you go.

 The Latest Offerings

Samsung’s latest consists of 128GB – 1TB MLC cards, utilizing 32-layer v-NAND and NVMe.  These cards are claiming up to 750,000 IOPS on 4k reads, and 115,000 IOPS on 4K writes – not bad.  SSD manufacturers like to use 4K page sizes when marketing their IOPS, since it will inevitably be faster than the 8K speed.  However, since we’re talking SQL Server here, you’ll want to see if you can obtain the 8K specs if you’re considering buying some local flash.

Intel currently has 400GB-2TB MLC offerings, utilizing traditional NAND and NVMe.  Up to 460,000/175,000 read/write IOPS achievable here (again, these are 4K page size specs, but +1 to Intel for also including 8K info on their marketing).  Intel has plans to answer Samsung with 32-layer v-NAND cards of their own in 2015.

Fusion IO is still in the game, offering some of the highest capacity SSDs on the market today (up to 6.4 TB MLC NAND).

Integration with SQL Server

The most common uses of PCIe flash with SQL Server includes tempDB storage (SQL Server 2012/2014), and as a buffer pool extension (SQL Server 2014).  Starting with SQL Server 2012, you can store tempDB on a local drive within a failover cluster.  We all know how much developers love tempDB, so reducing the latency from milliseconds to microseconds for tempDB access can be a huge win.  SQL Server 2014 gave us the buffer pool extension feature, allowing the use of local SSD as an extension of memory.  The main logic with either choice is the same – the more data you keep local will reduce the I/O on the SAN, and improve latency of read heavy workloads.

 

The featured image for this post is courtesy of: http://flic.kr/p/A5aTo.

Rate

Share

Share

Rate