Storage

Getting more performance with Micron® 5210 ION SSDs and a BI/DSS workload on Microsoft® SQL Server: platform tuning and tempdb placement

By Dilim Nwobu - 2018-07-23

With the release of the world’s first QLC SSD, the Micron 5210 ION, our team has been tinkering away to find ways to further maximize its value with different workloads and enterprise applications. As outlined in We’re Flexing Our Quads: Introducing the World’s First QLC solid state drive, the Micron 5210 SSD is optimized for read intensive workloads.

Business intelligence and decision support systems (BI/DSS) on Microsoft SQL Server is a widely-used application where the value of QLC SSDs really shines through since the workload profile is read-intensive with some writes to tempdb. For this test, I wanted to see how performance was impacted when I moved tempdb to different drives.

The Setup

Performance 1Dell® PowerEdge 740XD

2x Intel® Xeon 8168

384 GB RAM

Windows® Server 2016

SQL Server 2017 Enterprise

TPC-H 3000 scale factor dataset; ~6TB with column store indexing

My baseline configuration used 4x Micron 5210 SSDs in a Parity Storage Space to host the entire database; the log, tempdb, and the data. My second configuration stored the log and tempdb on 2x 5200 PRO in a Mirror Storage Space and the rest of the data on 4x Micron 5210 SSDs in a Parity Storage Space.

To simulate a BI/DSS workload, I ran our internally developed test automation that implements the queries and database schema outlined in the TPC-H benchmark. I ran a single stream executing all 22 of the TPC-H queries to measure the stream run time and disk throughput on both configurations.

The Results and Analysis

Performance 2We can see stream run time improves by 25% and the read throughput of the configuration increases 47%.

More specifically, although the writes to tempdb are only 2% of the entire workload, moving this 2% of the workload to general purpose or more write intensive drives, we gained 25% performance from the Micron 5210 SSDs.

Would You like to Know More?

We have only scratched the surface in demonstrating ways the Micron 5210 ION SSD built on QLC NAND can make an impact on enterprise workloads and configurations. I look forward to finding more ways the 5210 can fit different workload environments. In the meantime, check out some of our tech briefs on the 5210 and learn more about how these drives can address your needs.

Quad-Level Cell Storage Frees Your Read-Intensive Workloads from Slow HDDs

Latest-Generation Storage Technology Brings Value and Density

Micron Quad-Level Cell Technology Advances New Affordable Capacity Standard

Have questions about our testing or methodology? Leave a comment below or you can email us at ssd@micron.com.

Dilm Nwobu portrait

Dilim Nwobu

Dilim Nwobu started his career at Dell Technologies where he worked on storage, custom solutions and server platform development. At Micron, Dilim is a storage solution engineer where he focuses on testing Micron products with Microsoft technologies, namely SQL Server and Azure Stack HCI.

+