Whitepaper

HPE ProLiant DL380 GEN9 and HPE PCIe LE Workload Accelerator 24TB Data Warehouse Fast Track Reference Architecture

This reference architecture details the server, storage and software configurations for the HPE ProLiant DL380 Gen9 with Fusion ioMemory SX350 PCIe Application Accelerator devices. The combination delivers superb performance, with the ability to host large data warehouses or a consolidation of data warehouses such as this 24TB example with SQL Server 2014.

Executive Summary

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.

The Microsoft SQL Server Data Warehouse Fast Track (DWFT) 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/O and CPU consumption. This consumption based approach ensures your data warehouse can fully take advantage of your hardware investment.

Fusion ioMemory PCIe products from SanDisk® provide the following significant benefits for data warehouses deployed on SQL Server 2014:

  • I/O performance and resiliency
  • Lower capital cost
  • Lower operational costs
  • Simplified management
  • Predictable maintenance

Fusion ioMemory devices use flash like memory instead of like disk. This gives applications native access to data, delivering the lightning-fast response times that businesses depend on today.

Industries such as banking, social media, retail, transportation, healthcare, security, entertainment, and research and development, all benefit greatly from this solution. Service levels are increased, while complex and expensive storage sprawl is significantly reduced.

In addition, the Fusion ioMemory platform reduces energy consumption and total cost of ownership. The Fusion ioMemory platform integrates hardware and software to overcome the limitations of legacy architectures and specialized hardware.

Fusion ioMemory solutions significantly increase data center efficiency, while delivering enterprise-grade performance, reliability, availability, and manageability.

IT managers, database architects, and CTOs looking to explore and deploy data warehouses and BI applications now have added leverage – they can take advantage of Fusion ioMemory engineering, integration and optimization to quickly build and deploy their next data warehouses.

About the HPE ProLiant DL380 Gen9

The HPE ProLiant DL380 Gen9 Server delivers the latest performance and expandability in the HPE 2P rack portfolio.

Reliability, serviceability and near continuous availability, backed by a comprehensive warranty, making it ideal for any server environment.

Designed to reduce costs and complexity, leveraging Intel’s latest E5-2600 v3 processors with up to 70% performance gain, plus the latest HPE DDR4 SmartMemory supporting 1.5TB and up to 14% performance increase. Additional support is available for 12Gb SAS and 40Gb NIC, with a broad range of graphics and compute options.

Fusion ioMemory SX350 PCIe Application Accelerator

The Fusion ioMemory SX350 PCIe application accelerator is the scalable capacity leader for PCIe flash solutions. The Fusion ioMemory SX350 PCIe series provides a cost-effective solution for read-intensive application workloads that include web hosting, data mining, seismic data processing, content caching, 3D animation, and CAD/CAM.

The Fusion ioMemory SX350 PCIe application accelerator is available in capacities from 1.25TB–6.4TB, with ultra-low 79s/15μs read/write data access latency, superior reliability, and outstanding random read/write performance of up to 225K/385K IOPS. It also provides updated VSL™ (Virtual Storage Layer) software that delivers direct memory access, minimizes latency, and maximizes application throughput.

With this significant performance improvement, customers can reduce infrastructure and reduce power and cooling costs over a traditional hard disk drive infrastructure, for a lower total cost of ownership (TCO). With over 7,000 customers and over 250,000 units sold, this latest generation of PCIe application accelerators is designed to provide customers with the peace of mind that these products will perform in the field as intended.

New Data Warehouse Features in Microsoft® SQL Server® 2014.

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. Our solutions deliver an order-of-magnitude of 7x better query performance when using CCI. The CCI solution 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 – of new data while the query workload is running. This reduces the data latency from the time data is born to when it is available for querying.

About the Data Warehouse Fast Track Reference Architecture

The SQL Server Data Warehouse Fast Track reference architecture provides a scalable framework based on balancing I/O to achieve maximum performance from SMP-based servers. SQL Server Data Warehouse Fast Track 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.

More information on SQL Server DWFT can be found here:

http://www.microsoft.com/en-us/server-cloud/data-warehouse-fast-track.aspx

Reference Architecture

The following table shows the configuration details for the Fusion ioMemory SX350 3200 and HPE ProLiant DL380 Gen9 Fast Track Data Warehouse architecture.

Server HPE ProLiant DL380 Gen9
Operating System Microsoft Windows Server 2012 R2 Standard Edition
CPU Intel® Xeon® E5-2697 v3 @ 2.6 GHz
PCI-E Slots Up to eight PCIe 3.0 slots (two CPU and second riser card)
Drivers 2 x 1.2TB SAS (OS)
RAM 256GB

General Settings

Operating System Settings

The operating system used for this Fast Track Data Warehouse test was Microsoft Windows Server 2012 R2 Standard Edition. Standard installation steps were used to install the operating system with default values, followed by service packs and update patches.

Windows Configuration – Power Settings

The High Performance plan was chosen to reduce CPU throttling.

Fusion ioMemory SX350 Settings

Each device was formatted to its default capacity with a block size of 512B. The raw disk was then used as a mount point and an NTFS file system was applied using the default space and geometry, with an allocation unit size of 4096KB.

Power Override

Enabling the power override setting on the Fusion ioMemory SX350 product line allows the device to draw up to 55 watts of power under heavy workloads and is required to achieve the performance results below. All cards must be configured at the same time and a server reboot is required for the setting to be active and persist.

Example:

fio-config –p FIO_EXTERNAL_POWER_OVERRIDE SN:MW

where < SN > is the serial number of the card obtained from fio-status, and < MW > is the power in milliwatts. The following example configures the device with the given serial number to 55W:

fio-config –p FIO_EXTERNAL_POWER_OVERRIDE 1410G0092:55000

Multiple cards must be configured with the same command:

fio-config -p FIO_EXTERNAL_POWER_OVERRIDE 1407G0327:55000,1504G0154:55000

Storage Configuration

The table below describes the storage configuration used for various devices and slots.

Slot Device Capacity Mount Point Allocation Notes
2 Fusion ioMemory SX350 3.2TB C:\DB\DATA Data Files \ TempDB \ Logs RAID 5
3 Fusion ioMemory SX350 3.2TB C:\DB\DATA Data Files \ TempDB \ Logs RAID 5
5 Fusion ioMemory SX350 3.2TB C:\DB\DATA Data Files \ TempDB \ Logs RAID 5
6 Fusion ioMemory SX350 3.2TB C:\DB\DATA Data Files \ TempDB \ Logs RAID 5
N/A 2 x 10K SAS 1.2TB C:\ OS Mirrored

UEFI Configuration

  • Hyper-Threading was enabled.
  • Operating mode was changed to "Maximum Performance”.
  • Fan Offset was set to “Increased Cooling”.

SQL Server Settings

Database Configuration

A 1TB data warehouse schema was created for benchmarking using the Fast Track toolkit. The schema used a master filegroup with 4 additional filegroups that represented the four partitions.

TempDB Configuration

Eight 10GB tempdb files were stored across four Fusion ioMemory volumes. The tempdb transaction log file was stored on the volume designated for log files.

Memory Allocation

SQL Server was allocated 118GB of the available server memory.

Local Security Policy

The SQL Server maintenance account was granted the following privileges:

  • Enable Lock Pages in Memory
  • Perform Volume Maintenance Tasks

SQL Server 2014 Configuration Parameters

Parameter Setting Description
Memory Allocation 118GB This is the Fast Track-required value for a 2-socket, 1TB database. Memory is deliberately constrained to enforce I/O pressure on the subsystem.
Max Degree of Parallelism 28 When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism (the number of processors employed to run a single statement).
Resource Governor memory Allocation 12% Default is 25%. This is reduced to 12% for both Row Store and Column Store to reduce the maximum memory consumed per query.
Fast Track Required Start-up Parameters -T1117 -T1117 should be added to the startup options.
This trace flag ensures even growth of all files in a file group in case autogrow is enabled. The standard FTDW recommendation for database file growth is to pre-allocate rather than use autogrow (with the exception of tempdb).
Optional Trace Flags -T1118 Helps alleviate allocation bit map contention in tempdb by switching allocations to full extents (8 physically contiguous pages (64 KB)).

Measured Performance

During Fast Track Database Validation, Microsoft’s Reference Point tool drives multiple concurrent query workloads designed to identify bottlenecks. The tool establishes the key performance metrics in the table below.

Scan Rate Type Scan Rate Description
Rated User Capacity 24TB Represents the optimal Fast Track-certified data capacity of the configuration.
Allows for 5:1 compression, recommended free space and applies some memory and throughput based limits.
Row Store Relative Throughput 188 Percentage throughput of this configuration in comparison to the FTDW reference configuration.
Column Store Relative Throughput 259 Percentage throughput of this configuration in comparison to the FTDW reference configuration.
Maximum User Data Capacity 27TB Calculated, based on total disk capacity. Allows 5:1 compression. Factors recommended free space. Ignores the throughput limits that are applied to the Rated User Capacity.
Measured Throughput (Q/Hr/TB) 241 Number of combined benchmark queries completed during the measurement interval. Normalized to the 1 TB database.

SQL Server Data Warehouse Fast Track Certification

DWFT Certification #2014-046 HP ProLiant DL380 (Gen9) with Fusion ioMemory reference architecture for Microsoft SQL Server 2014 Repot Date:
03/11/2016
DWFT Rev. 5.4 DWFT Reference Architecture
System Provider System Name Processor Type Memory
Hewlett Packard Enterprise HP ProLiant DL380 (Gen9) E5-2697 v3 2.6 GHz (2/24/48) 384GB
Operating System SQL Server 2014 Enterprise Edition
Windows Server 2012 R2 SQL Server 2014 Enterprise Edition
Storage Provider Storage Information
SanDisk 4 x Fusion ioMemory SX350-3200 (RAIDS)
12,200 GB allocated to Data and TempDB
600 GB allocated to LOG
  Primary Metrics  
  Rated User Data Capacity1 (TB) Row Store Relative Throughput2 Column Store Relative Throughput3 Maximum User Data Capacity1 (TB)  
  24 188 259 27  
Row Store
Relative Throughput2 Measured Throughput (Queries/Hr/TB) Measured Scan Rate Physical (MB/Sec) Measured Scan Rate Logical (MB/Sec) Measured I/O Throughput (MB/Sec) Measured CPU (Avg.) (%)
188 241 4,756 6,294 5,525 91
Column Store
Relative Throughput2 Measured Throughput (Queries/Hr/TB) Measured Scan Rate Physical (MB/Sec) Measured Scan Rate Logical (MB/Sec) Measured I/O Throughput (MB/Sec) Measured CPU (Avg.) (%)
259 1,684 1,649 N/A N/A 93

Summary

Together, HPE and SanDisk dedicated hundreds of hours of testing to engineer the SQL Server DWFT solution to provide the most optimal reliability and performance. These series of tests pushed the HPE ProLiant DL380 Gen9 to peak performance without hardware failure. The reliability and performance experienced during testing is what can be expected in production environments.

The same configuration meets the need of both Row Store and Column Store configurations, delivering high physical read throughput in the Row Store configuration at 4.7GB/s, and high query rates in the Column Store configuration at 1,649 Q/Hr/TB.

The Fusion ioMemory PCIe device simplifies storage configuration by reducing the importance of sequential I/O, as evangelized in previous Data Warehouse Fast Track Reference Architectures.

The HPE ProLiant DL380 Gen9 with Fusion ioMemory technology delivers superb performance, with the ability to host large data warehouses or a consolidation of data warehouses.

Fusion ioMemory products provide the following significant benefits for data warehouses deployed on SQL Server 2014:

  • I/O performance and resiliency
  • Lower capital cost
  • Lower operational costs
  • Simplified management
  • Predictable maintenance

Bill of Materials

HPE ProLiant DL380 Gen9 – No High Availability Option

Qty SKU Description
1 719064-B21 HPE DL380 Gen9 8SFF CTO Server
1 719054-L21 HPE DL380 Gen9 E5-2697v3 FIO Kit
1 719054-B21 HPE DL380 Gen9 E5-2697v3 Kit
16 726719-B21 HPE 16GB 2Rx4 PC4-2133P-R Kit
2 718162-B21 P 1.2TB 6G SAS 10K rpm SFF (2.5-inch) SC Dual Port Enterprise 3yr Warranty Hard Drive
1 719073-B21 HPE DL380 Gen9 Secondary Riser
2 720479-B21 HPE 800W FS Plat Ht Plg Pwr Supply Kit
4 831737-B21 HPE 3.2TB Read Intensive-2 HH/HL PCIe Workload Accelerator

Footnotes

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 thoughput to the column store throughput of the reference configuration.

* Reported metrics are based on the qualification configuration which specifies database size and AQL Server memory.

BEREIT FÜR EIN UPGRADE MIT FLASH SPEICHER?

Ganz gleich, ob Sie ein Fortune 500-Unternehmen oder ein Startup mit fünf Personen sind, SanDisk bietet Lösungen an, mit der Sie Ihre Infrastruktur optimal nutzen können.

PER
E-MAIL

Stellen Sie uns einig Fragen und wir melden uns mit Antworten bei Ihnen zurück.

Lassen Sie uns ein Gespräch führen
+1 800 578 6007

Warten Sie nicht, lassen Sie uns jetzt ein Gespräch führen und damit beginnen, die perfekte Flash-Lösung zu erstellen.

Kontakte weltweit

Hier finden Sie Kontaktinformationen von Niederlassungen auf der ganzen Welt.

VERKAUFSANFRAGEN

Ganz gleich, ob Sie einige erste Fragen stellen möchten oder bereit sind, eine SanDisk Lösung zu besprechen, die auf die Bedürfnisse Ihres Unternehmens zugeschnitten ist, das SanDisk Verkaufsteam unterstützt Sie gern.

Gerne beantworten wir Ihre Fragen. Dazu müssen Sie nur das nachstehende Formular ausfüllen, damit wir beginnen können. Wenn Sie umgehend mit dem Vertriebsteam sprechen möchten, wählen Sie bitte: +1 800 578 6007

Feld darf nicht leer sein.
Feld darf nicht leer sein.
Geben Sie eine gültige E-Mail-Adresse ein.
Feld darf nur Zahlen enthalten.
Feld darf nicht leer sein.
Feld darf nicht leer sein.
Feld darf nicht leer sein.
Feld darf nicht leer sein.
Feld darf nicht leer sein.
Feld darf nicht leer sein.

Bitte geben Sie Ihre Interessenbereiche an

Fragen und Kommentare:

Sie müssen eine Option auswählen.

Vielen Dank! Wir haben Ihre Anfrage erhalten.