A New Benchmark With Real Business Impact Using SQL Server 2017 on Linux and the Power of NVMe™.
Another TPC-H Benchmark™ with SQL Server 2017 on Linux by Western Digital? Yes! Today we are pleased to announce that Western Digital and Cisco set the first 3,000GB world record using a two-socket server and the first 3,000GB world record using SQL Server 2017 on Linux. This TPC-H benchmark is our first official TPC benchmark using the HGST Ultrastar® SN200 Series PCIe NVMe SSD.
SQL Server 2017 on Linux – Showing Real World Benefits
For many it was a big surprise when Microsoft announced that SQL Server would run on Linux. Yet with the multitude of changes the IT world is undergowing, choice and flexibility have become key goals and enablers. SQL Server has undergone many transformations, and SQL Server 2017 represents a major step in bringing powerful tools to wherever its users are and the prevelance of open source tools.
Our new benchmark shows the real-world benefits of this shift, and is enabled by the latest demonstration of hardware innovations from Western Digital and Cisco.
Breaking Down the Metrics
While delivering less than 1% difference in TPC-H throughput and 4% difference in the TPC-H Composite (QphH) metrics, Cisco’s C240 M5 Rack Server with HGST Ultrastar SN200 Series PCIe SSD delivers on par performance when compared to the previous Cisco 3,000GB SQL Server TPC-H world record while:
Reducing CPU power consumption by 38%
Reducing physical core count by 23%
Reducing total system DRAM by 50%
Reducing rack density by 50%
The table provides a side-by-side comparison of the previous 3,000GB TPC-H benchmark using SQL Server 2016 and Cisco’s C460 M4 Rack Server to the new 3,000GB TPC-H world record on SQL Server 2017 and Cisco’s C240 M5 Rack Server.
Ultrastar SN200 Series PCIe NVMe SSD Performance Results
During performance runs, we captured disk level performance metrics to observe latency, IOPS, and bandwidth:
Average Disk Latency (ms)
Total Peak Bandwidth (MB)
Total Peak IOPS
You may ask, each Cisco HHHL AIC 3.2T HGST SN260 NVMe Extreme Perf High Endurance is capable of 6GB/s of read bandwidth, why did four adapters only achieve 15.4GB/s of read bandwidth?
For the TPC-H benchmarks we used Clustered Column-Store Indexes (CCI) to achieve the performance numbers in the Full Disclosure Report. With CCI, the TPC-H dataset’s compression ratio is 1:2, meaning most of the dataset fits in memory thus the reads are logical. The physical I/O activity is mostly the creation of large temporal objects which couldn’t fit into memory. The temporal objects are read from storage back into memory. This I/O behavior is sporadic during the query executions. Reliable, fast storage is required to reduce reads back into DRAM.
4 x Cisco HHHL AIC 3.2T HGST SN260 NVMe Extreme Perf High Endurance
2 x 3.8TB 2.5-inch Enterprise Value 6G SATA SSD
1 x Cisco 12G SAS Modular Raid Controller with 2GB Cache for C240 M5
To minimize I/O latency the storage design is straightforward. The TPC-H database persisted on four Cisco HHHL AIC 3.2T HGST SN260 NVMe Extreme Perf High Endurance drives. The backup device is comprised of 2 x 1.2TB 12G 10K HDD. The OS and database transaction log was stored on 2 x 3576GB 6G SAS SSDs in RAID 1 configuration. The TPC-H specification only requires redundancy for the Operating System and the database transaction log for recovery.
Helping You Succeed in a Data-Centric World
Western Digital and its SanDisk® and former Fusion- io® brands have set 9 previous TPC-H world records. If you’re a Fusion ioMemory customer, your devices can be replaced with Ultrastar SSDs with little effort or risk in most applications while gaining massive performance and usability improvements.
This benchmark is the first of many TPC-H benchmarks featuring the HGST Ultrastar SN200 Series PCIe SSD, showcasing the power of NVMe. Check back often as we plan more TPC benchmarks with Cisco, as we enable businesses with faster intelligence, and better cost efficiencies in a data-centric world.
Check out our partner Cisco’s blog to learn more about this world record and their tradition of top SQL Server TPC-H benchmarks.
Upgrading from SanDisk Fusion ioMemory to HGST Ultrastar SN200 Series PCIe SSD?
Brian is a Senior Technologist for the Fabric integration and Solutions Team.Brian has over a decade experience in database, server, and storage technologies, including DBA and Architect positions at Microsoft, Fusion-io and SanDisk.He was previously part of the Data Propulsion Labs’ Microsoft Advisory Team where he authored white papers demonstrating SanDisk and HGST solutions ability to deliver cutting–edge storage performance Microsoft technologies such as SQL Server and Hyper-V.