TABLE OF CONTENTS
2INTRODUCTION TO SUPERMICRO 2U ULTRASOLUTION

32U ULTRA 22TB DWFT REFERENCE
ARCHITECTURE
Key FeaturesStorage Configuration
Database Configuration
Tempdb Configuration
SQL Server Settings

TECHNICAL REPORT22TB DWFT FOR MICROSOFT®
SQL SERVER® 2014 USING
SUPERMICRO® ULTRA 2028U
WITH HYBRID NVME

Server Configuration

EXECUTIVE SUMMARY
88

SUPERMICRO MICROSOFT CERTIFIEDSOLUTIONSSUPERMICRO SQL DWFT CERTIFIEDREFERENCE ARCHITECTURESSupermicro® 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-TNR4T+ has achieved excellent scores for the DWFTbenchmarks. With total 2x 4TB NVMe SSD drives, the Reference Architecture iscertified to be sufficient to host a 22TB data warehouse instance.

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

Supermicro Recommends Windows.

22TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® Ultra 2028U with Hybrid NVMe

INTRODUCTION TO SUPERMICRO 2U ULTRA SOLUTION

What's New• Support up to 4x NVMe drives withindustry's first-to-market true hot-swap
capabilities. Deliver over 6x better IOPS
performance, and 7x lower latency than
SATA SSDs.
• Flexible drive deployment: users canfreely combine and hot-swap SATA,
SAS and NVMe drives for various usage
scenarios within a single system with 26x
2.5” drive bays.
• 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.

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 servertechnology 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 2x 4TB NVMe SSDs, the Reference
Architecture is certified to be sufficient to host a 22TB Microsoft SQL Server 2014
data warehouse instance.

• Supermicro Server Manager (SSM)
provides a comprehensive solution tomanage and maintain Supermicro servers
in an IT data center from a single consoleview.

Figure 1. Supermicro 2U Ultra SuperServer 2028U-TNR4T+2

22TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® Ultra 2028U with Hybrid NVMe

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

Key Features••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 previous generations.

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

••

4 Intel DC P3500 2TB NVMe drives for SQL data storage.

••

2x 480GB SATA3 SSDs and 8x 300GB SAS3 HDDs for OS and SQL Server logs.

••

4x 10GBase-T LAN Ports.

••

2x Redundant 1000W Titanium Level Digital Power Supplies.

LUN0Data
LUN1

LUN2Log
LUN3

LUN4

LUN5

TempDB

Disk Drive

Logic View

OS

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

2TB
NVMe

2TB
NVMe

2TB
NVMe

2TB
NVMe

8x 300GB SAS3 HDD (RAID 10)

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

322TB DWFT For Microsoft® SQL Server® 2014 UsingSupermicro® Ultra 2028U with Hybrid NVMeStorage Configuration
The Ultra SYS-2028U-TNR4T+ DWFT is configured to use 4 NVMe 2TB JBOD as themain storage for database, as shown in Figure 2 (LUN1 through LUN4).The system
is also configured with a LUN0 volume, which consists of 2x 480GB SATA3 SSDs in
RAID 1 for the operating system; and LUN5, a 8x 300GB HDDs RAID 10 volume for
SQL Server logs. TempDB is distributed evenly throughout the 6 LUNs.

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

1 on LUN1, 1 on LUN2,1 on LUN3, 1 on LUN4

Part_ci1FG

1 on LUN1, 1 on LUN2,
1 on LUN3, 1 on LUN4

Part_ci2FG

1 on LUN1, 1 on LUN2,
1 on LUN3, 1 on LUN4

Part_ci3FG

1 on LUN1, 1 on LUN2,
1 on LUN3, 1 on LUN4

Part_ci4FG

1 on LUN1, 1 on LUN2,
1 on LUN3, 1 on LUN4

Part_ci5FG

1 on LUN1, 1 on LUN2,1 on LUN3, 1 on LUN4

Part_ci6FG

1 on LUN1, 1 on LUN2,1 on LUN3, 1 on LUN4

Part_ci7FG

1 on LUN1, 1 on LUN2,1 on LUN3, 1 on LUN4

FT_Demo_LOG

1 on LUN5

Tempdb Configuration
The tempdb database is configured to utilize 30 data files for the 2 socket 28 core
CPU configuration. A total of 150GB of tempdb data files are evenly distributedon each LUN. The tempdb log file is placed on the log volume, which is LUN5.
“autogrow” is enabled for each data file.

4

22TB DWFT For Microsoft® SQL Server® 2014 UsingSupermicro® Ultra 2028U with Hybrid 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.

••–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. Thesetting is changed in the default resource pool as shown in Figure 3.

5

22TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® Ultra 2028U with Hybrid NVMe

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

Figure 3. Resource Governor Settings

Figure 4. Server Power Settings

6

22TB DWFT For Microsoft® SQL Server® 2014 Using
Supermicro® Ultra 2028U with Hybrid NVMe

DWFT Certification#2014-055

Supermicro 2028U-TNR4T+

Report Date:
5/23/2016

DWFT Reference Architecture

DWFT Rev. 5.4

System Provider

System Name

Processor Type

MemorySupermicro 2028U-TNR4T+

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

256 GB

Operating System

SQL Server Edition

Windows Server 2012 R2

SQL Server 2014 Enterprise Edition

Storage Provider

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

Primary Metrics
Rated
User Data
Capacity1

Row Store
Relative
Throughput2

Column Store
Relative
Throughput322166

196

25

Measured

Measured

Measured

Measured I/O

MeasuredThroughput

Scan Rate
PhysicalScan Rate
LogicalThroughput

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

(%)

166

4,348

5,436

4,892

64

Relative

Measured

Measured

Measured

Measured I/O

MeasuredThroughput

Scan Rate
PhysicalScan Rate
LogicalThroughput

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

(%)

1,272

1,378

N/A

N/A

72

(TB)

Maximum
User Data
Capacity1(TB)

Row Store
Relative
Throughput

2Column StoreThroughput

196

2

The reference configuration is a 2 socket system rated for 25TB using 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 5. 2U Ultra 22TB DWFT Reference Architecture Certification Results.
*Measured CPU utilization between 64/72%
Note: different MS SQL 2014 versions have huge impacts for overall CPU utilizations. The MS SQL 2014 Enterprise supports
up to 20 physical cores/40 logic cores, which is inadequate for the 28 physical cores in our Reference Architecture. With SQL
server 2014 Enterprise Core edition that supports unlimited cores, the CPU utilization jumped to 94/99% for row and column
respectively.

722TB DWFT For Microsoft® SQL Server® 2014 UsingSupermicro® Ultra 2028U with Hybrid 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 ProductFamily
www.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 Windows
Server, 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 SMBand 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_22TB_160601_Rev2