White Papers

SanDisk® Enterprise Solid State Drives: Providing Cost-Effective Performance Increases for Microsoft SQL Server Database Deployments

Background

This paper outlines the benefits and cost of replacing your database volume’s traditional spinning hard disk drives with SanDisk solid-state drives (SSDs). Testing, using the TPC-E industry benchmark, was conducted for this paper during Q1 of 2014.

 

Microsoft SQL Server – A Functional System Overview

Microsoft SQL Server is a widely used database platform for mission-critical applications running on x86 servers and Microsoft Windows. Providing robust storage is essential to the Quality of Service (QoS) provided by SQL Server databases. The goal is to provide customers with fast access to information and high levels of uptime, providing four-nines or more (99.99%) of uptime). To achieve this result, it is important to leverage RAID configurations in the server-side storage. This means that the RAID array can be fully restored, as quickly as possible, in the event of a hardware failure within the system. This failure may likely be isolated to one of the drives in the deployment, allowing the rest of the storage within the server to continue to operate, even if overall performance is reduced.

When configured correctly, SQL Server operations continue while the array is being rebuilt in the background. It’s important to note that this is the situation in which the database service’s Quality of Service (QoS) is affected the most. During the rebuilding operations, traditional hard-disk drives (HDDs) suffer a huge performance hit. Due to multiple operations taking place, even as processing continues, the rebuild time for HDDs is also significantly longer than that of solid-state disks (SSDs).

Remember: when the array is rebuilding, your mission-critical data is at risk. In the pages that follow, we will show how customers can minimize the risk, and increase overall performance of the system several times over comparable HDD configurations – all while saving money.

 

TPC-E test of Microsoft SQL Server

TPC-E is the go-to benchmark for Microsoft SQL Server databases, simulating online transaction processing workloads (OLTP) leveraging a relational database as a centralized database for all the transactions. More information can be obtained about TPC-E setup using the links provided in the reference section of this paper.

This paper focuses on the hardware configuration and the results of the benchmark testing. The TPC-E tests conducted were using the default database configuration with a 75,000-customer database being accessed by up to 1,000 clients simultaneously.

The standard two-hour test was run in optimal configuration so that performance data from both the hard drive (HDD) configuration and the solid state drive (SSD) configuration could be gathered. Once that data was collected and analyzed, the test was run again for four hours.

During the four-hour test, one drive was removed from the six-drive database volume to simulate a drive failure. After a few minutes, the drive was replaced -- and this automatically initiated the rebuild on the RAID controller. The test continued while the rebuild was being performed by the RAID controller, and the performance data was again analyzed.

When the benchmark test data was analyzed, the results overwhelmingly favor the SSD configuration, which requires less equipment, less power, and is less expensive than the comparable HDD configuration.

Details about the Testing

This section provides the specifications and technical details describing the TPC-E benchmark test we performed, and provides the results of the testing.

First, let’s look at the configuration and then we’ll examine the results.

 

Server Configuration Disclosure

Computer: Dell System PowerEdge R720

CPU: Intel Xeon E5-2620 (Sandy Bridge-EP, C2) 2000 MHz (20.00x100.0) @ 1200 MHz (12.00x100.0)
CPU: Intel Xeon E5-2620 (Sandy Bridge-EP, C2) 2000 MHz (20.00x100.0) @ 1299 MHz (13.00x100.0)

Motherboard: DELL 0C4Y3R

Chipset: Intel X79 (Patsburg)

Memory: 96GB @ 666 MHz, 9.0-9-9-24 - 6 pcs - 16384 MB PC12800 DDR3 SDRAM - Samsung M393B2G70BH0-YK0 Graphics: Matrox G200eR2 Video Adapter [DELL] Matrox MGA-G200eR2, 16 MB SDRAM

Drive: ATA ST9500620NS, Disk drive
Drive: DELL PERC H310, Disk drive
Drive: DELL PERC H310, Disk drive
Drive: DELL PERC H810, Disk drive
Drive: DELL PERC H810, Disk drive
Drive: DELL PERC H810, Disk drive

Network: Broadcom BCM5720 NetXtreme PCI-E Gigabit Ethernet Controller
Network: Intel Ethernet 10G 2P X520 Adapter
Network: Intel Ethernet 10G 2P X520 Adapter

OS: Microsoft Windows Server 2012 R2 Standard (x64) Build 9600

 

Performance Results

As you can see from the charts, the SSD configuration loses only 5% of overall performance during a drive rebuild – even though that beginning-to-end rebuilding process may takes hours to complete. It’s 3.4x faster than it would be for a HDD database for normal operations, and it’s 7.3x faster than the HDD database in the rebuild state.

The Quality of Service (QoS) suffers severely on the traditional hard disk drive database while rebuilding. Measured performance dropped more than 55%, compared with performance on the HDDs.

The impact of deploying the SSD is immediately felt by customers accessing the database, because the response times are significantly increased. This loss of performance remains static until the drive is fully rebuilt – which takes approximately 10 times as long on the HDD configuration than on the SSD configuration.

 

Cost Analysis

The server equipment and client equipment configurations are identical in both tests, with the exception of the database volume, so the cost shown here is based strictly on the cost differential between the two types of configurations—one for HDDs and another for SSDs.

For the Solid State Drive (SSD) configuration, a single Dell MD1220 enclosure was used with four (4) Optimus 400GB SSDs configured in a RAID 5. This provided the required storage for the database – which was approximately 811 GB.

For the Hard Drive (HDD) configuration, 32 Dell 300GB 15K RPM HDDs were used in two (2) Dell MD1220 enclosures – with 16 drives in each of the two enclosures. These HDDs were short-stroked to 10% of their available capacity, as is industry standard practice to achieve maximum performance for a database volume running on spinning drives.

AS of April, 2014, the Dell MD1220 enclosure’s starting price was $3,550 each. The SanDisk Optimus 400GB SSD was priced at $1452.99. The Dell (Toshiba) 15K RPM HDD cost was $307.99.Using those figures, the difference in the cost of the two configurations heavily favors the SanDisk SSDs.

  • The total cost for the SSD Configuration, as of April, 2014, was: $12,911.96 for drives and single enclosure.
  • The total cost for the HDD Configuration, as of April, 2014, was: $16,955.68 for drives and dual enclosures.

That analysis shows that, with those configurations, there was a savings of nearly 24% in equipment cost alone, by using the SSD drives.

 

Conclusion

SanDisk SSDs provide significant performance improvements for Microsoft SQL Server deployments, delivering higher Quality of Service and better reliability than traditional HDD spinning disks. Given that approach to testing a TPC-E industry-standard benchmark, this paper shows that SSD deployments can decrease the total cost of ownership (TCO) for database customers.

 

References

Enclosure pricing from Dell:
http://www.dell.com/us/business/p/powervault-md1220/pd

SanDisk SSD pricing from CDW:
http://www.cdw.com/shop/products/SanDisk-Optimus-solid-state-drive-400-GB-SAS-2/3266305.aspx

Dell (Toshiba) MK3001GRRB HDD Pricing from CDW:
http://www.cdw.com/shop/products/Toshiba-MK3001GRRB-hard-drive-300-GB-SAS-2/3010513.aspx?enkwrd=ALLPROD%3a%7cMK3001GRRB%7cAll%20Product%20Catalog

TPC-e Testing information from:
http://www.tpc.org/tpce/

READY TO FLASH FORWARD?

Whether you’re a Fortune 500 or five person startup, SanDisk has solutions that will help you get the most out of your infrastructure.

VIA
EMAIL

Go ahead, ask us some questions and we'll get back to you with answers.

Let's Talk
800.578.6007

Don't wait, let's just talk now and start building the perfect flash solution.

Global Contact

Find contact information for offices all over the world.

SALES INQUIRIES

Whether you'd like to ask a few initial questions or are ready to discuss a SanDisk solution tailored to your organizations's needs, the SanDisk sales team is standing by to help.

We're happy to answer your questions, so please fill out the form below so we can get started. If you need to talk to the sales team immediately, please phone: 800.578.6007

Field cannot be empty.
Field cannot be empty.
Enter a valid email address.
Field can only contain numbers.
Field cannot be empty.
Field cannot be empty.
Field cannot be empty.
Field cannot be empty.
Field cannot be empty.
Field cannot be empty.

Please indicate your areas of interest:

You must choose an option.

Questions or comments:

You must choose an option.

Thank you. We have received your request.