Fusion-io Flash Pushes SQL Server 2014 In-Memory Further 

There is no such thing as too much performance improvement. Microsoft's SQL Server 2014 relational database is shipping, and enterprises are now trying to figure out how to best deploy new "Hekaton" in-memory database engine as well as the Buffer Pool Extensions that help speed up the old SQL Server engine. Fusion-io has released some performance tests on how flash can be used to further accelerate the new and old engines.

"Databases are our bread and butter," Lance Smith, president of Fusion-io, explains to EnterpriseTech in going over the test results. "This is where we have been building tremendous momentum for flash for both hyperscale and enterprise customers."

Up until now, there have been two easy ways to make flash speedup databases, says Smith. The first is to put the whole database on flash and be done with it. Or you can add flash to the servers and figure out a more optimal configuration and still get a big performance boost. The latter is the course that Fusion-io recommends – it wants flash in every server, not necessarily servers with only flash – but some customers don't always heed this advice or have a good reason not to. (Such as, for instance, they are loaded like Facebook or Apple, the two biggest customers of Fusion-io.)

Now, with SQL Server 2014, there is a third and fourth way. The third way is to use flash to accelerate the old database engine using Buffer Pool Extensions, and the final way is to use flash to keep Hekaton humming along better with the tables that it is storing and processing inside of main memory.

Microsoft has been showing off the effect of putting database tables into main memory with the Hekaton feature and its new database engine, which EnterpriseTech showed you in great detail back in March. While you can get a 35X speedup by moving tables in memory, Hekaton is limited to two-socket servers and does not support a number of SQL features, such as large objects, triggers, constraints, foreign keys, sparse columns, and outer joins, just to name a few.

Given this, and the fact that a server typically has 256 GB or 512 GB of main memory, it might make sense to speed up the SQL Server database by putting flash cards behind the Buffer Pool Extensions, which keep the data normally stored on disk drives on flash to give the processors quick access to it. It is not uncommon, says Smith, for a server to have 1 TB or 2 TB of flash, and this is cheaper than main memory at the same capacity. Fusion-io has not released performance results for using flash cards for Buffer Pool Extensions, but Microsoft says to expect something on the order of a 3X speedup.

The addition of PCI-Express flash memory to a SQL Server 2014 database server using the new Hekaton database engine significantly boosts performance, according to benchmark reports just released by Fusion-io. Specifically, flash cards can help with the offline check pointing process for the database, which generates a lot of sequential disk I/O and increases transaction log activity, according to Fusion-io. While disk-style SSDs can be used to offload this data, flash cards are faster because they are on the PCI-Express bus, not sitting behind a RAID disk controller.

To show the effect of flash cards on SQL Server 2014 performance using the Hekaton engine, Fusion-io fired up a benchmark test based on the TPC-C online transaction processing benchmark and ran it on a two-socket Dell PowerEdge R720 using Xeon E5 processors with 256 GB of memory. The setup had 40 GB of database tables running in-memory and the rest stored either out on a Fusion-io ioDrive2 card or an unnamed enterprise-class disk array. The ioDrive2 card had 2.4 TB of capacity, split evenly into one partition for logs and the other for file groups. The disk array had two RAID 10 groups, each with a dozen drives. Some 12,000 users were simulated to drive the database transaction rate, and this was the point at which the disk-based setup ran out of I/O and started hurting the performance of the in-memory processing.

Here is a summary view of how the disk and flash variants stacked up on the OLTP test:

As you can see, the flash helps cut transaction log and filegroup read and write latencies for Hekaton and boosts the bandwidth for the transaction logs. This table shows how steady-freddy the ioDrive 2 is compared to the disks for filegroup write latencies:

And this one shows the much higher bandwidth on those filegroup writes:

With 12,000 users hammering away on this two-socket server, here is how the disk and flash card configurations stacked up:

The idea here is just shifting tables to memory is not enough to get screaming performance – remember that both of these setups have already moved database tables to the Hekaton engine. If you want to push performance even further – and who doesn't really? – then a flash tier looks to be in order.

This is precisely the kind of boost that Fusion-io is going to need if it is going to get its revenues and its costs in line and compete better against the other flash acceleration options out there in the market today. In the company's fiscal third quarter ended in March, revenues were up 14.7 percent to $100.5 million, but net losses grew to $30.6 million. This is the fifth straight quarter of losses for Fusion-io, and while the company has $225.1 million of cash in the bank to ride out the losses, Wall Street likes to see companies growing and making money, and SanDisk is ramping up production of its UlltraDIMM flash-based memory channel storage and others are starting to push SSDs equipped with NVMe connections that cut down significantly on latencies when sitting behind disk controllers. This is going to be a tough fight, but then again, when has that not been the case in the IT sector?