TABLE OF CONTENTS
2INTRODUCTION TO SUPERMICRO 2U ULTRASOLUTION

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

8SUPERMICRO MICROSOFT CERTIFIEDSOLUTIONS

8SUPERMICRO SQL DWFT CERTIFIEDREFERENCE ARCHITECTURESTECHNICAL REPORT40TB DWFT FOR MICROSOFT®
SQL SERVER® 2014 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 Intel® NVMe SSDs, the latest
Intel Xeon® E5-2600 v4 Broadwell CPUs, as well as Microsoft® SQL Server® 2014.
Supermicro SuperServer 2028U-TN24R4T+ has achieved excellent scores forthe DWFT benchmarks. With 12 NVMe SSD drives, the Reference Architecture iscertified to be sufficient to host a 40TB data warehouse instance.
Some of the key results are highlighted below:••Rated User Data Capacity: 40 TB••

Row Store Measured I/O Throughput: 6,180 (MB/Sec)••Column Store Relative Throughput: 213 Queries/Hr/TB

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

Supermicro Recommends Windows.

40TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® Ultra 2028U with 24 NVMe

INTRODUCTION TO SUPERMICRO 2U ULTRA SOLUTION

What's New• Support up to 24x NVMe drives withindustry's first-to-market true hot-swap
capabilities. Delivering over 6x better
IOPS performance, and 7x lower latency
than SATA SSDs.
• Scalable I/O expansion capabilities withsupport up to 6 PCI-E 3.0 x8 slots.
• Lowering OpEx with Redundant 1000W
Titanium Level (96%) Digital PowerSupplies.
• Supermicro Server Manager (SSM)
provides a comprehensive solution tomanage and maintain Supermicro servers
in an IT data center from a single consoleview.

Microsoft Data Warehouse Fast Track (DWFT) for SQL Server® 2014 is designed toprovide customers optimized and validated system architectures for addressing
data warehouse workload challenges. With the new “Clustered Column Store Index”
feature, SQL Server 2014 is able to deliver higher data compression ratio to support
the exponential growth of data, and to meet new demands for lower data latency
and faster query response times.
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 Intel® NVMe (Non-Volatile Memory Express) SSDs
with the latest Intel® Xeon® E5-2600 v4 Broadwell CPUs, has achieved excellent
scores from DWFT benchmarks. With a total of 12 Intel NVMe SSDs, the Reference
Architecture is certified to be sufficient to host a 40TB Microsoft SQL Server 2014
data warehouse instance with measured CPU utilization rate up to 94/98% for row
and column stores respectively.

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

2

40TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® Ultra 2028U with 24 NVMe

2U ULTRA 40TB 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® E5-2690 v4 family
processors (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, and lower
power consumption than previousgenerations.

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

• 8x 32GB DDR4-2133MHz memory DIMMs
for a total of 256GB RAM. The system cansupport up to 24 DIMMs for up to 3TB
DDR4 memory.

The first 6 drive bays from left (see Figure 1) are allocated for 6 NVMe drives (4xP3700 800GB + 2x P3500 2TB) to form LUN1 through LUN6. The 11th through16th drive bays are allocated for the rest (4x P3700 800GB + P3500 2x 2TB) to form
LUN11 through LUN16. The 12 NVMe are distributed this way to fully utilize theavailable PCI-E lanes provided by each CPU. In addition, drive bay 20 to 23 arepopulated 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 480GB SATA3 SSDs in RAID 1 are deployed forhosting Windows Server 2012 R2.

• 2x Redundant 1000W Titanium Level
Digital Power Supplies.

Data and TempDBOSSQL Log

LUN1

LUN2

LUN3

LUN4

LUN5

LUN6

LUN11

LUN12

LUN13

LUN14LUN15

LUN164x 2TB
NVMe

Disk Drive• 4x 10GBase-T LAN Ports.

Logic View

• 12 NVMe SSDs are used for SQL databasestorage, including 4 Intel DC P3500 2TB,
and 8 Intel DC P3700 800GB SSD drives.
The system can support up to 24 NVMe
SSD drives.

2x 480GB SATA3
SSDs (RAID 1)

8x 800GB
NVMe

4x 2TB SAS3 SSDs (RAID 10)

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

3

40TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® 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

12 data files, 1 data file on each data LUN

Part_ci1FG

12 data files, 1 data file on each data LUN

Part_ci2FG

12 data files, 1 data file on each data LUN

Part_ci3FG

12 data files, 1 data file on each data LUN

Part_ci4FG

12 data files, 1 data file on each data LUN

Part_ci5FG

12 data files, 1 data file on each data LUN

Part_ci6FG

12 data files, 1 data file on each data LUN

Part_ci7FG

12 data files, 1 data file on each data LUN

FT_Demo_LOG

1 transaction log on log LUNTempdb Configuration
The TempDB database is configured to utilize 12 data files for the 2 socket 28 core
CPU configuration. A total of 120GB of TempDB data files are evenly distributed oneach LUN. The TempDB log file is placed on the log volume. “autogrow” is enabledfor each data file.

4

40TB DWFT For Microsoft® SQL Server® 2014 UsingSupermicro® Ultra 2028U with 24 NVMe

SQL Server Settings
Trace Flags
Below trace flags were added to startup parameters:••–EThe -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.

••–T1117
When a file in the filegroup meets the autogrow threshold, all files in thefilegroup grow to balance the data allocation.

••–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

40TB DWFT For Microsoft® SQL Server® 2014 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

40TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® Ultra 2028U with 24 NVMe

DWFT Certification#2014-055

Supermicro 2028U-TN24R4T+

Report Date:
5/31/2016

DWFT Reference Architecture

DWFT Rev. 5.4System Provider

System Name

Processor Type

MemorySupermicro 2028U-TN24R4T+

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

768 GB

Operating System

SQL Server Edition

Windows Server 2012 R2

SQL Server 2014 Enterprise Edition

Storage Provider

Storage Information
8x 800GB+ 4x 2TB NVMe for data and tempdb
2x 480 GB SATA-3 SSD for OS (RAID 1)
4x 2 TB SAS3 SSD for log (RAID 10)

Primary Metrics
Rated
User Data

Row Store
Relative

1

Throughput

Capacity

Column Store
Relative
2

Throughput

(TB)

40

3MaximumUser Data
Capacity

1(TB)

210

21348

Row Store
Relative
Throughput

2210

Measured

Measured

Measured

Measured I/O

MeasuredThroughput

Scan Rate
PhysicalScan Rate
LogicalThroughput

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

(%)

5,571

6,790

6,180

94

Column Store
Relative
Throughput

213

2

Measured

Measured

Measured

Measured I/O

MeasuredThroughput

Scan Rate
PhysicalScan Rate
LogicalThroughput

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

(%)

1,388

1,240

N/A

N/A

99

The reference configuration is a 2 socket system rated for 25TB using the DWFT V4 methodology1Assumes 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 40TB DWFT Reference Architecture Certification Results.

7

40TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® 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 areoptimized 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 REFERENCEARCHITECTURES
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_40TB_160819_Rev2