«WHITE PAPER Cisco UCS C240 M4 Rack Server with Lightning Ascend™ Gen. II SAS SSDs from SanDisk® 36TB Data Warehouse Fast Track Reference ...»
Cisco UCS C240 M4 Rack Server with
Lightning Ascend™ Gen. II SAS SSDs from SanDisk®
36TB Data Warehouse Fast Track Reference Architecture
Based on the SQL Server 2016 Data Warehouse Fast Track (DWFT)
Western Digital Technologies, Inc.
951 SanDisk Drive, Milpitas, CA 95035
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016)
Table of Contents
About the Cisco UCS C240 M4 Rack Server
Lightning® Product Family of SAS SSDs from SanDisk
About the Data Warehouse Fast Track Reference Architecture
Server UEFI Configuration
Operating System Settings
Windows Configuration – Power Settings
SQL Storage Pool - Virtual Disks
SQL Server Settings
Local Security Policy
SQL Server 2016 Configuration Parameters
Bill of Materials
Cisco UCS C240 M4 – Non High Availability Option
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) Executive Summary This guide details the server, storage, and software configurations for the Cisco UCS® C240 M4 Rack Server with Lightning Ascend Gen. II 12Gb/s SAS SSDs (1.6TB) from SanDisk which Microsoft® has validated for a Fast Trackrated user capacity of 36TB.
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.
About the Cisco UCS C240 M4 Rack Server Storage and I/O-Optimized Cisco UCS C-Series Rack Servers The Cisco UCS C240 M4 Rack Server is a 2-socket, 2-rack-unit (2RU) rack server. It offers outstanding performance and expandability for a wide range of storage and I/O-intensive infrastructure workloads, from big data to collaboration. The enterprise-class Cisco UCS C240 M4 server extends the capabilities of the Cisco Unified Computing System™ (Cisco UCS) portfolio in a 2RU form factor with the addition of the Intel® Xeon® processor E5-2600 v4 and v3 product family, which delivers a superb combination of performance, flexibility, and efficiency.
Product Overview The Cisco UCS C240 M4 offers outstanding levels of internal memory and storage expandability with exceptional
performance. It has the following features:
Up to 24 DDR4 DIMMs at speeds of up to 2400 MHz, for improved performance and lower power consumption Up to 6 PCI Express (PCIe) 3.0 slots (4 full-height, full-length) Up to 24 small form factor (SFF) drives or 12 large form factor (LFF) drives, plus two (optional) internal SATA boot drives Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) Support for 12-Gbps SAS drives A modular LAN-on-motherboard (mLOM) slot for installing a next-generation Cisco virtual interface card (VIC) or third-party network interface card (NIC) without consuming a PCIe slot 2 x 1 Gigabit Ethernet embedded LOM ports Support for up to two double-wide NVIDIA graphics processing units (GPUs), providing a graphics-rich experience to more virtual users Excellent reliability, availability, and serviceability (RAS) features with tool-free CPU insertion, an easyto-use latching lid, hot-swappable and hot-pluggable components, and redundant Cisco® Flexible Flash (FlexFlash) SD cards
For additional details, refer to the following datasheet:
http://www.cisco.com/c/en/us/products/collateral/servers-unified-computing/ucs-c240-m4-rackserver/datasheet-c78-732455.html Lightning® Product Family of SAS SSDs from SanDisk The Lightning Ascend Gen. II 12Gb/s SAS SSD doubles interface speed, providing the highest performance for mission-critical hyperscale, cloud, and virtualized data center application workloads. This product offers a
feature-rich robust design, including the following:
SanDisk’s innovative parallel processing architecture Full data path protection (T10-DIF support) Temperature-based power control SED instant secure erase MTBF of 2.5 million hours Power fail protection The SSD is backward-compatible with 6Gb/s SAS and offers a single firmware binary platform for seamless
integration and reduced qualification times. A summary of features is shown below:
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) For more information, visit https://www.sandisk.com/business/datacenter/products/flash-devices/ssds/sas-ssd/lightning-gen2 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 Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) Microsoft SQL Server 2016: Data Warehousing with Improved Column Store Technology Microsoft SQL Server 2016 has made significant improvements in data warehousing technologies and performance, including column-store features as well as many other improvements.
Column-store indices offer great advantages over traditional row stores for analytics and data warehousing queries. They are ideally suited for the star schemas, and tables with billions of rows which are commonly seen.
Among their advantages for analytics are:
Up to 10X compression in data size Data warehouses are very large by nature, and the compression offered by column store index technologies offers both space and cost savings, but also significantly increased performance, due to the dramatically reduced IO requirements given by the compression, coupled by the ability to only scan the specific columns required by each query. This compression also reduces the amount of memory required to hold a given number of rows from the source data warehouse.
Additional Indices SQL Server 2016 adds the capability to add additional (B-Tree) indices to column store-based tables, which enables efficient single-row lookup.
In addition to these architectural features, we have further optimized the processing of queries in column-store
indices in the following ways:
Operator Pushdown Pushdown refers to moving both filter and aggregation query operations closer to the data, so that many of the filters and calculations can be done in the scan operators, dramatically reducing the volume of data which needs to be handled further on in query processing.
Batch Mode Processing SQL Server 2016 includes enhancements in batch-mode processing which processes many rows at a time rather than serially doing calculations on each individual row. These batch operations are further optimized by leveraging Single Instruction Multiple Data (SIMD) vector processing CPU instructions in the Intel® architectures.
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) Reference Architecture The following table shows the configuration details for the Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs, using the Fast Track Data Warehouse architecture.
Server UEFI Configuration Hyper-Threading was enabled.
Operating mode was changed to "Maximum Performance”.
Fan Offset was set to “Increased Cooling”.
General Settings Operating System Settings This Fast Track Data Warehouse test used the Microsoft Windows Server 2012 R2 Standard Edition OS. 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.
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) Storage Configuration The Windows 2012R2 OS was configured on a pair of 1.2TB 6G SAS 10K SFF HDDs (mirrored).
The Lightning Ascend Gen. II SAS SSDs were configured using Storage Spaces. This technology allows Windows Server to virtualize storage by grouping industry-standard disks into storage pools and then creating virtual disks (“storage spaces”) from the available capacity in those storage pools.
Storage Spaces enable cost-effective, highly available, scalable and flexible storage solutions for business-critical deployments.
This server is available with a single Cisco 12G SAS Modular RAID controller, configured to pass-through mode.
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.
TempDB Configuration In total, eight 10GB tempdb files were created and stored on the volume designated for data files (C:\DB\DATA).
The tempdb transaction log file was stored on the volume designated for log files (C:\DB\LOGS).
Memory Allocation SQL Server was allocated 118GB of the available server memory (512GB). This amount is allocated as part of the Fast Track test criteria to drive backend disk activity during the Row Store query runs.
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) Local Security Policy
The SQL Server maintenance account was granted the following privileges:
Enable Lock Pages in Memory - prevents SQL Server buffer pool pages from paging out.
Perform Volume Maintenance Tasks - enables Instant File Initialization.
SQL Server 2016 Configuration Parameters
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) 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.
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) SQL Server Data Warehouse Fast Track Certification Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) Summary Together, Cisco 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 Cisco UCS C240 M4 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 measured I/O throughput in the Row Store configuration at 5.5GB/s, and high query rates in the Column Store configuration at 2365 Q/Hr/TB.
Cisco UCS C240 M4 Rack Server with Lightning Ascend Gen. II SAS SSDs from SanDisk – 36TB DWFT RA (SQL Server 2016) Bill of Materials Cisco UCS C240 M4 – Non High Availability Option
©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. Lightning and Lightning Ascend are trademarks of Western Digital Corporation or its affiliates. Other brand names mentioned herein are for identification purposes only and may be the trademark(s) of their respective holder(s). 5111EN 20160609