TABLE OF CONTENTS
1EXECUTIVE SUMMARY

2INTRODUCTION TO SUPERMICRO 2U ULTRASOLUTION

32U ULTRA 70TB DWFT REFERENCE
ARCHITECTURE
Storage Configuration
Database Configuration
Tempdb Configuration
SQL Server Settings
Server Configuration

8SUPERMICRO MICROSOFT CERTIFIEDSOLUTIONS

8SUPERMICRO SQL DWFT CERTIFIEDREFERENCE ARCHITECTURESTECHNICAL REPORT70TB DWFT FOR MICROSOFT®
SQL SERVER® 2016 USING
SUPERMICRO® ULTRA 2028U
WITH 24 NVMEEXECUTIVE SUMMARYSupermicro® has collaborated with Microsoft® to develop a SuperServer® DWFTReference Architecture, which utilizes high-performance and highly efficient 2U
Ultra SuperServers, low latency and high throughput NVMe SSDs, the latest Intel®
Xeon® processor E5-2600 v4 CPUs, as well as Microsoft® SQL Server® 2016.
By configuring 10 NVMe SSD drives out of the available 24 all-flash NVMe drivebays, this DWFT Reference Architecture is certified to be sufficient to host a 70TB
data warehouse instance. For every data warehouse instance tested, the 2U Ultrasystem exhibits a great performance per dollar advantage.
Some of the key results are highlighted below:••

Rated User Data Capacity: 70TB••

Row Store Measured Throughput: 19,180 Queries/Hr(274 Queries/Hr/TB )••Column Store Measured Throughput: 154,420 Queries/Hr(2,206 Queries/Hr/TB)

August 2016
Super Micro Computer, Inc.
980 Rock Avenue
San Jose, CA 95131 USA
www.supermicro.com

Supermicro Recommends Windows.

70TB DWFT For Microsoft® SQL Server® 2016 Using
Supermicro® Ultra 2028U with 24 NVMe

INTRODUCTION TO SUPERMICRO 2U ULTRA SOLUTION

What's New• Support Dual Intel® Xeon® processor
E5-2600 v4 product familly (Broadwell).
• Support up to 24x NVMe drives withindustry's first-to-market true hot-swap
capabilities.
• Scalable I/O expansion capabilities withsupport up to 6 PCI-E 3.0 x8 slots.
• Lowering OpEx with Redundant 1000W
Titanium Level (96%) Digital Power
Supplies.
• Supermicro Server Manager (SSM)
provides a comprehensive solution to
manage and maintain Supermicro servers
in an IT data center from a single consoleview.

Microsoft SQL Server 2016 has made significant improvements in data warehousing
performance compared to Microsoft SQL Server 2014. The improved Column Store
index technologies offered up to 10x higher compression ratio in data size, whichsignificantly reduced the number of I/O transactions and the amount of memory
required, hence increased performance dramatically.
SQL Server 2016 also added additional (B-Tree) indices to column storebased tables, which enabled more efficient single-row lookup. Some of otherperformance improvements on column store include Operator Pushdown andBatch Mode Processing that further optimized the processing of column storebased queries.
Supermicro®, the leading innovator in high-performance, high-efficiency server
technology is a premier provider of advanced Server Building Block Solutions®
for HPC, Data Center, Cloud Computing, Enterprise IT and Embedded Systems
worldwide. Supermicro’s proven high level of quality and performance has become
the platform of choice for supercomputer clusters and enterprise databases as well
as mission-critical, front-end server applications.
The Supermicro® 2U Ultra SuperServer® DWFT Reference Architecture, which
integrates low latency/ high speed NVMe (Non-Volatile Memory Express) SSDs withthe latest Intel® Xeon® processor E5-2600 v4 Broadwell CPUs, has achieved excellent
scores from DWFT benchmarks. With a total of 10 NVMe SSDs, the Reference
Architecture is certified to be sufficient to host a 70TB Microsoft SQL Server 2016
data warehouse instance with measured CPU utilization rate up to 92/100% for rowand column stores respectively.

Figure 1. Supermicro 2U Ultra SuperServer 2028U-TN24R4T+ Front View

2

70TB DWFT For Microsoft® SQL Server® 2016 Using
Supermicro® Ultra 2028U with 24 NVMe

2U ULTRA 70TB DWFT REFERENCE ARCHITECTURE
The Supermicro 2U Ultra SYS-2028U-TN24R4T+ based DWFT referencearchitecture shown in Figure 1 offers numerous best-in-class advantages including
performance, power efficiency, platform security and storage/IO scalability in theindustry.

Key Features

Storage Configuration• Dual Intel® Xeon® processor E5-2690 v4(14 cores, 35 MB Smart Cache, 2.6 GHz, 9.6
GT/s Intel® QPI), which leverages the latest
14nm process technology that enables
greater functionality, high density, andlower power consumption than previous
generations.

On the Ultra SYS-2028U-TN24R4T+ Reference Architecture, 24 drive bays arepartitioned into 3 storage pools as shown in Figure 2.

• 24x 32GB DDR4-2133MHz memory
DIMMs for a total of 768GB RAM. Thesystem can support up to 24 DIMMs forup to 3TB DDR4 memory.

The first 5 drive bays from left (see Figure 1) are allocated for 5 x 3.2TB NVMe drives
to form LUN1 through LUN5. The 11th through 15th drive bays are allocated for therest to form LUN11 through LUN15. The 10 NVMe are distributed this way to fully
utilize the available PCI-E lanes provided by each CPU. In addition, drive bay 20 to23 are populated with 4x 2TB SAS3 SSDs for the SQL Server log.
The system also includes 2 additional drive bays in the back of the chassis withRAID support as shown in Figure 3. 2x 400GB SATA3 SSDs in RAID 1 are deployed forhosting Windows Server 2012 R2.

• 10x 3.2TB NVMe SSDs are used for SQL
database storage. The system can supportup to 24 NVMe SSD drives.
• 4x RJ45 10 Gigabit Ethernet LAN Ports.

Logic View

OSLog

Data and Tempdb
LUN1

LUN2

LUN3

LUN4

LUN5

LUN11

LUN12

LUN13

LUN14

LUN15

3.2TB
NVMe

3.2TB
NVMe

3.2TB
NVMe

3.2TB
NVMe

3.2TB
NVMe

3.2TB
NVMe

3.2TB
NVMe

3.2TB
NVMe

3.2TB
NVMe

3.2TB
NVMeDisk Drive• 2x Redundant 1000W Titanium Level
Digital Power Supplies.

2x 400GB SATA-3
SSD (RAID 1)

4x 2TB SAS-3 SSD (RAID 10)

Figure 2. 70TB 2U Ultra DWFT Reference Architecture Storage Layout.

370TB DWFT For Microsoft® SQL Server® 2016 UsingSupermicro® Ultra 2028U with 24 NVMe

Figure 3. Supermicro 2U Ultra SuperServer 2028U-TN24R4T+ Rear View

Database Configuration
The table below show the configuration for data and log files of FT_Demo databasethat is used for this benchmark.
FILE GROUP# OF DATA FILES

Base

10 data files, 1 data file on each data LUN

Part_ci1FG

10 data files, 1 data file on each data LUN

Part_ci2FG

10 data files, 1 data file on each data LUN

Part_ci3FG

10 data files, 1 data file on each data LUN

Part_ci4FG

10 data files, 1 data file on each data LUN

Part_ci5FG

10 data files, 1 data file on each data LUN

Part_ci6FG

10 data files, 1 data file on each data LUN

Part_ci7FG

10 data files, 1 data file on each data LUN

FT_Demo_LOG

1 transaction log on log LUN

Tempdb Configuration
The TempDB database is configured to utilize 10 data files for the 2 socket 28 core
CPU configuration. A total of 7450GB of TempDB data files are evenly distributed oneach LUN. The TempDB log file is placed on the log volume. “autogrow” is enabled
for each data file.

4

70TB DWFT For Microsoft® SQL Server® 2016 Using
Supermicro® Ultra 2028U with 24 NVMe

SQL Server Settings
Trace Flags
Below trace flags were added to startup parameters:••–E
The -E parameter increases the number of extents that are allocated for eachfile in a filegroup so as to improve the data sequence and the performance ofsequential I/O.

••–T834
When this trace flag is set, SQL Server uses Windows large-page memory
for the buffer pool. This setting improves performance by increasing theefficiency of the translation look-aside buffer (TLB) in the CPU.

Memory Settings••Maximum server memory for this reference architecture was set to 118GB.

••The SQL server service account was assigned the Lock Pages in Memory
policy.

Thread Mode
Configured SQL server to be running in thread mode.
sp_configure 'show advanced options', 1;gosp_configure 'lightweight pooling', 0;
goreconfigurego

Max Degree of Parallelism
Max degree of Parallelism (MDOP) was set to 28 for both row store and columnstore.
Resource Governor
The Memory Grant percentage value was set to 12% of the memory allocated. The
setting is changed in the default resource pool as shown in Figure 4.

5

70TB DWFT For Microsoft® SQL Server® 2016 Using
Supermicro® Ultra 2028U with 24 NVMe

Server Configuration
Power settings
The power plan is set to High performance plan to reduce CPU throttling as shownin Figure 5.

Figure 4. Resource Governor Settings

Figure 5. Server Power Settings

670TB DWFT For Microsoft® SQL Server® 2016 Using
Supermicro® Ultra 2028U with 24 NVMe

DWFT Certification#2016-006

Supermicro 2028U-TN24R4T+

Report Date:
8/10/2016

DWFT Reference Architecture

DWFT Rev. 5.4

System Provider

System Name

Processor Type

MemorySupermicro 2028U-TN24R4T+

Intel Xeon E5-2690 v4
2.6 GHz (2S/28C/56T)

1024 GBOperating System

SQL Server Edition

Windows Server 2012 R2

SQL Server 2016 Enterprise Edition

Storage Provider

Storage Information

10x 3.2TB NVMe for data and tempdb
2x 400 GB SATA-3 SSD for OS (RAID 1)
4x 2 TB SAS3 SSD for log (RAID 10)Rated
User Data

Row Store
Relative

Column Store
Relative

Maximum
User Data

70225

339

103

Measured

Measured

Measured

Measured I/O

MeasuredThroughput

Scan Rate
PhysicalScan Rate
LogicalThroughput

CPU (Avg.)(Queries/Hr/TB)
274(MB/Sec)(MB/Sec)(MB/Sec)(%)

Measured

Measured

Measured

Measured I/O

MeasuredThroughput

Scan Rate
PhysicalScan Rate
LogicalThroughput

CPU (Avg.)(Queries/Hr/TB)(MB/Sec)(MB/Sec)(MB/Sec)

Capacity1(TB)

Relative
Throughput

2225
Relative
Throughput2

339

Primary Metrics

2,206

Throughput2

Throughput3Row Store

5,820

7,409

Column Store

3,033

N/A

Capacity1(TB)

6,614

N/A92(%)

100

The reference configuration is a 2 socket system rated for 25TB using SQL Server 2014 and the DWFT V4 methodology
1Assumes a data compression ratio of 5:1
2
Percent ratio of the throughput to the row store throughput of the reference configuration.
3
Percent ratio of the throughput to the column store throughput of the reference configuration.
*
Reported metrics are based on the qualification configuration which specifies database size and SQL Server memory.

Figure 6. 2U Ultra 70TB DWFT Reference Architecture Certification Results.

7

70TB DWFT For Microsoft® SQL Server® 2016 UsingSupermicro® Ultra 2028U with 24 NVMe

FOR MORE INFORMATIONSUPERMICRO MICROSOFT CERTIFIED SOLUTIONS• 2U Ultra SuperServers
www.supermicro.com/Ultra

Supermicro and Microsoft have partnered together to deliver industry leading"fully certified solutions" on highly optimized and flexible Supermicro serverand storage Systems. Supermicro systems are designed to provide significant
advantage in the areas of power efficiency, performance and overall system
optimization. These design principles coupled with bringing innovativetechnologies at an accelerated pace drives time to market value for MicrosoftSolutions. In case of some of these disruptive technologies like NVMe, Supermicro
is an established leader with the broadest range of products that customers canchoose from.

• All-Flash NVMe SuperStorage Solutions
www.supermicro.com/NVMe• 2U Simply Double SuperStorage
supermicro.com/products/nfo/Simply_Double.cfm• Supermicro® SuperServer®
SYS-2028U-TNR4T+ Hybrid Datasheet
www.supermicro.com/products/
system/2u/2028/sys-2028u-tnr4t_.cfm• Supermicro® SuperServer®
SYS-2028U-TN24R4T+ All-Flash Datasheet
www.supermicro.com/products/system/2u/2028/SYS-2028U-TN24R4T.
cfm• Microsoft Data Warehouse FastTrackwww.microsoft.com/en-us/cloudplatform/data-warehouse-fast-track• Microsoft SQL Server 2016www.microsoft.com/en-us/cloudplatform/sql-server• Microsoft Windows Server 2016www.microsoft.com/en-us/cloudplatform/windows-server• Intel® Xeon® Processor E5-2600 v4 Product
Familywww.intel.com/content/www/us/en/
processors/xeon/xeon-e5-solutions.html• Intel® NVMe SSDs
www.intel.com/content/www/us/en/
solid-state-drives/solid-state-drives-ssd.
html

Microsoft Certified Solutions running on Supermicro hardware include WindowsServer, SQL Server Data Warehouse, Exchange Server, Hyper converged solutions
like Storage Spaces Direct, Storage Server and Azure Stack. These solutions are
optimized for Enterprise, Hybrid Cloud, Private and Public Cloud markets. We alsodeliver the Windows operating system software preloaded for fast growing SMB
and Enterprise customers.
Supermicro has consistently innovated in the areas of designing server boards,
chassis, highly efficient power supplies, networking equipments and storagesystems. The building block approach is one of the key innovations to server systemarchitecture. This vertically integrated design approach accelerates the delivery ofapplication optimized Microsoft Solutions based on customer requirements.

SUPERMICRO SQL DWFT CERTIFIED REFERENCE
ARCHITECTURES
Drive impact in your business using the power of a robust, cloud-enabled SQL
Server 2016 database solution that offers enhanced performance, robust security,
cloud enablement, and deeper insights across multiple types of data.
••70TB DWFT For Microsoft SQL Server 2016 Using 2U Ultra 2028U All NVMe
www.supermicro.com/white_paper/70TB_DWFT.pdf••40TB DWFT For Microsoft SQL Server 2014 Using 2U Ultra 2028U All NVMe
www.supermicro.com/white_paper/40TB_DWFT.pdf••22TB DWFT For Microsoft SQL Server 2014 Using 2U Ultra 2028U Hybrid
NVMe/SAS
www.supermicro.com/white_paper/22TB_DWFT.pdf

8About Super Micro Computer, Inc.
Supermicro® (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology is a premier provider ofadvanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/Big Data, HPC and Embedded
Systems worldwide. Supermicro is committed to protecting the environment through its “We Keep IT Green®” initiative and provides
customers with the most energy-efficient, environmentally-friendly solutions available on the market.
www.supermicro.com

The information contained in this document is subject to change without notice.
Results are provided for informational purposes only. Any difference in system hardware or software design or configuration may affectactual performance. Performance tests are measured using specific computer systems, components, software, operations, functions, andworkloads. Any change to any of those factors may cause the results to vary. You should consult other information and performance tests
to assist you in fully evaluating your contemplated purchases, including the performance of that product when combined with otherproducts.
No part of this document covered by copyright may be reproduced in any form or by any means — graphic, electronic, or mechanical,including photocopying, recording, taping, or storage in an electronic retrieval system — without prior written permission of thecopyright owner.
Supermicro, the Supermicro logo, Building Block Solutions, We Keep IT Green, SuperServer, TwinPro™, TwinPro²™, SuperDoctor are
trademarks and/or registered trademarks of Super Micro Computer, Inc.
Ultrabook, Celeron, Celeron Inside, Core Inside, Intel, Intel Logo, Intel Atom, Intel Atom Inside, Intel Core, Intel Inside, Intel Inside Logo, IntelvPro, Itanium, Itanium Inside, Pentium, Pentium Inside, vPro Inside, Xeon, Xeon Phi, and Xeon Inside aretrademarks of Intel Corporation in the U.S. and/or other countries.
© Copyright 2017 Super Micro Computer, Inc. All rights reserved.
Printed in USA

Please Recycle		

14_MS-DWFT_70TB_160826_Rev8