|Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML|
|News Links Schedule Site Map Contact|
SQL Server Performace on Solid State Drives (SSD)
Andy Novick, May 2010
Solid State Disks (SSDs) are devices that take advantage of the high speed nature of flash memory chips to provide fast persistent data storage. SSD’s come in a variety of forms that are suitable for different purposes. The most common forms of SSD are:
Here is the corresponding test with the Fusion-IO SSD board:
The table shows that the SSD board is able to achieve 3,580 IO/Seconds for write and 5,738 IO/Second for read both on the 128 byte test. The throughput in megabytes/second is also much higher than for the disks at 447 for write and 717 for read. Again, the throughput seems to be the limiting factor. Another difference of the board level SSD’s is their short latency. The average latency for both read and write is less than an aggregate of 40 drives.
Cost can also be a factor in the decision to use SSD’s. The 300 GB SSD board costs around $14,000. That is a significant savings over using 40 SAN hosted drives for tempdb and there’s still a performance boost.
Theoretical I/O latency numbers may be a good indicator but real world examples should be more convincing. The following table compares the run times of several stored procedures from the development system, which has a heavy load. The procedures were chosen because they make heavy use of tempdb and thus are the best candidates for performance improvement. This should be considered anecdotal evidence because they comparisons are not based on processing the same data. However, the improvement did seem to hold up over time.
These examples show that it is possible to reduce the run time of procedures that make use of tempdb. Of course, since the Fusion-IO board only has tempdb it’s only when using tempdb that there will be any improvement. SQL Server uses tempdb for temp tables that that user code creates with names that begin with # or ##. It also uses tempdb to store table variables, table valued parameters, and xml variables. When needed tempdb is used for work tables for sorting, hashing, group by, distinct, snapshot isolation, and index rebuild operations.
Although still new SSDs are a hardware option with promise for significant speed improvements in SQL Server runtimes. There is also a potential for cost savings due to the ability of SSDs to provide a high number of I/O operations and throughput that would require a very large number of hard disks.
What type of chips are used in SSD products?
Today SSD’s are made with NAND flash chips but there are two types, MLC and SLC. Multi-Level cell (MLC) chips are the most common and they store two bits to each flash chip cell. You’ll find MLC chips in cameras, phones and most 2.5” SATA disk replacement products. They may wear out after being used for tens of thousands of erase/write cycles. Single-Level Cell (SLC) chips store only one bit per flash cell but last around one hundred thousand erase/write cycles. The larger SSD's boards are made with MLC chips.
I’ve heard that Flash disks wear out. How long does the Fusion-IO board last?
Although the 300 GB Fusion-IO board uses MLC chips from Samsung that’s only part of the longevity answer. SSD products don’t write the same disk “sector” onto the same flash chips each time the sector is modified. Instead it writes to the recently erased chips and keeps track of where each sector is located. Even if writing was taking place 100 percent of the time, 24x7, it would take several years to wear out the chips. Fusion-IO gave an example of a 320 GB board undergoing 5 terabytes of writes per day. It would last for almost 20 years. 5 terabytes is a realistic number. It equals a sustained rate of 60 megabytes per second of write 24 hours a day. Reads don’t wear out flash chips.
Can SSD’s be used in a SQL Server clustered environment?
SSD’s in LANs and External Rack Mounted SSD products can be used in clusters. However, the Fusion-IO board is internal to a single computer. Standard clustered configurations require that all databases, including tempdb, be on storage that can be shared between members of the cluster so the Fusion-IO board doesn’t qualify. There are non-standard cluster configurations (hacks that work but don’t call Microsoft) where tempdb doesn’t have to be shared and the Fusion-IO board could be used
About the Author
Andrew Novick is a SQL Server MVP and Principal of Novick Software where he
develops business applications as a consultant using SQL Server ASP/ASP.NET,
XML. He is the author of Transact-SQL User Defined Functions.