SQL Server 2017 on Linux – 3TB TPC-H World Record!

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.

SQL Server on Linux 3TB TPC-H comparison
A side-by-side comparison of the previous and new benchmark

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
1.7915,400244,800

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.

Hardware Configuration

  • Cisco® C240 M5
    • 2 x Intel® Xeon® Scalable Processor Platinum 8180M CPUs (2.5 GHz, 38.5MB L3 cache, 205W)
    • 24 x 64GB DRAM @2666 MHZ
    • 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

Storage Configuration

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.

SQL Server 2017 on Linux 3TB TPC-H configuration

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.

Further reading:

Upgrading from SanDisk Fusion ioMemory to HGST Ultrastar SN200 Series PCIe SSD?

Interested in learning more about the successor product to the Fusion ioMemory PCIe SSD acquired through the SanDisk acquisition? Here’s what you need to know: https://www.sandisk.com/business/datacenter/resources/white-papers/upgrading-from-fusion-ioupgrading-from-fusion-iomemory-to-ultras

About the Ultrastar SN200 Series PCIe SSD

http://www.hgst.com/sites/default/files/resources/Ultrastar-SN200-Series-datasheet.pdf

About the Cisco USC C240 M5 Rack Server

https://www.cisco.com/c/en/us/products/collateral/servers-unified-computing/ucs-c-series-rack-servers/datasheet-c78-739279.html

About the TPC Benchmark

http://www.tpc.org/information/about/abouttpc.asp

More on SQL Server 2017

https://www.microsoft.com/en-us/sql-server/sql-server-2017

Source link

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.