HP DL380 Gen8 and HP LE PCIe Workload Accelerator 28TB/45TB Data Warehouse Fast Track Reference Architecture

Executive Summary

The Microsoft SQL Server Data Warehouse Fast Track (DWTF) reference architecture is designed to eliminate the complexity of properly sizing hardware, which helps reduce unnecessary scale-out of storage and servers. The sizing techniques used in SQL Server DWFT will properly size servers, based on I/0 and CPU consumption. This consumption- based approach ensures your data warehouse can fully take advantage of your hardware investment.

This document is for individuals (BI Architects, DBAs, Report Developers, and IT Directors) involved in decision making who are looking for guidance when designing enterprise, business-intelligence applications.

Why Choose Our Solutions?

Together, HP and SanDisk^® dedicated hundreds of hours of testing to engineer the SQL Server DWFT solutions to ensure maximum reliability and performance. These series of tests pushed the HP ProLiant DL380 Gen8 and HP PCIe LE Workload Accelerator to their peak performance without failure of the hardware. The reliability and performance experienced during testing is what can be expected in production environments.

About the HP Proliant Dl380 Gen8

What is your server bottleneck - storage, processing, or expansion? No matter the bottleneck, the HP ProLiant DL380p Gen8 Server series can help. It sets the data center standard for 2U, 2-socket rack servers with the latest in serviceability, enhanced configuration flexibility, customer-inspired design, and unmatched performance.

The HP ProLiant DL380p Gen8 Server offers the perfect solution for the dynamic compute requirements of growing small businesses as well as demanding data centers.

What's New?¹

• Eliminate infrastructure complexity with HP 0neView 1.10, now available on all HP ProLiant DL Gen8 Servers.
• Increase performance of up to 35%1 at the same or lower power levels with the new Intel® Xeon® E5-2600 v2 processors.
• GPU support is provided in primary and secondary risers to accelerate compute and enable large-data processing for a wide array of applications
• New HP Common Slot Power Supplies provide 96% server power efficiency, and new infrastructure power efficiencies are achieved with 277VAC and 380VDC input voltages.
• HP SmartMemory enables 16.6% better memory performance.
• Up to 4x workload performance with HP SmartCache, a controller-based caching solution that caches hot data onto lower-latency SSDs.

Features

• Improved capacity and performance on compute and storage.
• Intel® Xeon® processor E5-2600 v2 product family offering increased performance, improved security and efficiency; ideal for demanding workloads.
• HP SmartMemory, with speeds up to 1866MHz. This prevents data loss and downtime with enhanced error handling, while improving workload performance and power efficiency.
• GPU compute support to maximize performance.
• HP SmartCache, a controller-based caching solution. This caches hot data onto lower latency SSDs providing up to 4x workload performance.²

New Data Warehouse Features in SQl Server 20l4

Microsoft added clustered column store indexes (CCI) in SQL Server 2014, which are designed to decrease query response times and deliver deeper levels of data compression. CCI eliminates the need to build summary tables, thus further reducing ETL run times.

• CCI is optimized for query performance. 0ur solutions deliver 7x better query performance when using CCI. CCI accomplishes this by using a columnar format to compress the data by 10x or more, processing a set of rows in batches, and reading only the columns that are referenced in the query.
• CCI is updateable allowing concurrent insert - both bulk import and trickle insert - of new data while query workload is running. This reduces the data latency from the time data is born to when it is available for querying.

About the SQl Server Data Warehouse Fast Track Reference Architecture

The SQL Server DWFT reference architecture provides a scalable framework centered on balancing I/0 to achieve maximum performance from SMP-based servers. SQL Server DWFT eliminates the complexity of sizing servers with data warehouses by providing a set of data consumption rates that properly balances performance between the disk subsystem, CPU, and memory.

This architecture is based on the HP ProLiant DL380 Gen8 and HP PCIe LE Workload Accelerator PX600-2600/PX600-5200 storage controllers. These configurations are optimized for data warehouse (scan I/0) workloads and are rated, by Microsoft for up to 45TB of compressed data. More information on SQL Server DWFT can be found here: http://www.microsoft.com/en-us/server-cloud/data-warehouse-fast-track.aspx

Data Warehouse Fast Track Reference Architecture Configurations

Storage Configuration

Storage layout for 28TB Certification

Storage layout for 45TB Certification

A-SAS controller was used for the database transaction log in both configurations, with 8 x 300GB 15K spindles in RAID 10.

Power Override
Enabling the power override setting on the HP PCIe LE Workload Accelerator product line is required to achieve the performance results below. A server reboot is required for the setting to be active and persist. For instructions on enabling the power override, see the HP PCIe LE Workload Accelerator User Guide for instructions.

Example:

fio-config -p FIO_EXTERNAL_POWER_OVERRIDE <device serial number>:<power (miiliwatts)>
fio-config -p FIO_EXTERNAL_POWER_OVERRIDE 1234Z5678:40000, 9876Z-5432:40000

Database Configuration

TempDB Configuration

Four tempdb files, 5GB each, were stored on each data volume. In total, 24 tempdb data files were spread across six data mount points. The tempdb transaction log file was stored on the volume designated from log and staging files.

SQl Server Settings

Resource Governor
The Memory Grant % value was set to 12% of the memory allocated for row store runs and 25% for column store runs. The settings were changed in the default resource pool.

Max Degree of Parallelism (MDOP)
MD0P was set to 24 for row store and 48 for column store. These values provided the best scan rates for the respective runs without pegging the processors @ 100% utilization.

Example:

--for row store runs
EXEC sp_configure 'max degree of parallelism', 24 GO
RECONFIGURE WITH OVERRIDE GO
--for column store runs
EXEC sp_configure 'max degree of parallelism', 48 GO
RECONFIGURE WITH OVERRIDE

GO

Memory Configuration

• SQL Server was allocated 90% of the server memory.
• The SQL Server service account was assigned the Lock Pages in Memory policy.

Trace Flags

Trace flag -T1117 was used to increase performance. This flag forces all data files in a file group to grow at once, which reduces "hot spots" of data pages. This ensures that all databases with more than one data file will grow properly across all the data mounts, which in turn ensures maximum physical I/0 performance. Trace flag "-E" was omitted, as testing revealed a sharp increase in queue depth and latency which decreases scan performance. Therefore trace flag "-E" is not recommended with our flash technology.

Server Configuration

Windows Server 20l2 R2 Configuration Power Settings

The High Performance plan was chosen to reduce CPU throttling.

BIOS Configuration

• Hyper-threading was enabled.
• System Profile was set to High Performance mode.
• Fan 0ffset was set to "Increased Cooling".

SQl Server Data Warehouse Fast Track Reference Architecture Results

28TB Certification

The reference configuration is a 2 socket system rated for 25TB using the FTDW V4 methodology

1 Assumes 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.

45TB Certification

The reference configuration is a 2 socket system rated for 25TB using the FTDW V4 methodology

1 Assumes 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.

Summary

These solutions went through hundreds of hours of testing and engineering to provide the most optimal and reliable configuration for the HP and SanDisk SQL Server DWFT Reference Architecture. The HP ProLiant DL380 Gen8 and HP PCIe LE Workload Accelerators deliver 5000+MB/s of consistent database performance while providing the best reliability in the industry. The HP PCIe LE Workload Accelerator simplifies storage configuration by reducing the importance of sequential I/0, as evangelized in previous DWFT Reference Architectures.

With a rated user capacity of 28TB and 45TB, these 2U configurations deliver the best mix of performance and data capacity, which reduces the need to scale your data warehouse. In fact, these solutions allow for massive database consolidation projects, allowing your organization to save on licensing cost.

The results highlight how HP and SanDisk can deliver enterprise-level solutions that serve as the foundation for data warehouse or database consolidation projects.

Bill of Materials

Specifications are subject to change. ©2014 - 2016 Western Digital Corporation or its affiliates. All rights reserved. SanDisk and the SanDisk logo are trademarks of Western Digital Corporation or its affiliates, registered in the U.S. and other countries. Fusion ioMemory is a trademark of Western Digital Corporation or its affiliates. Other brand names mentioned herein are for identification purposes only and may be the trademarks of their respective holder(s). HP DL380 Gen8 20160621 Western Digital Technologies, Inc. is the seller of record and licensee in the Americas of SanDisk® products.