This reference architecture details the server, storage and software configurations for the Lenovo System x3650 M5 with Optimus Ascend SSDs from SanDisk (6Gb/s SAS, 400GB), which Microsoft has validated for a Fast Track-rated user data capacity of 5TB, This guide is based on the SQL Server 2014 Data Warehouse Fast Track (DWFT) reference architecture.
This guide details the server, storage and software configurations for the Lenovo System x3650 M5 with Optimus Ascend™ SSDs from SanDisk (6Gb/s SAS, 400GB), which Microsoft has validated for a Fast Track-rated user data capacity of 5TB.
By installing a single CPU in this 2-socket system, and with 18 small form factor (SFF) drive bays unused, this 5TB architecture offers scalable capacity and performance while minimizing software licensing costs.
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 the 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.
With the powerful, versatile new 2U two-socket Lenovo System x3650 M5 rack server, you can run even more workloads, 24 x 7, and gain faster business insights. Integrated with the Intel® Xeon® processor E5-2600 v3 product family and industry-leading two-socket storage capacity, the x3650 M5 fast-forwards your business. You can select from an impressive array of storage configurations (up to 28 small form factor [SFF] drive bays) that optimize diverse workloads from cloud to big data.
Combining balanced performance and flexibility, the x3650 M5 is a great choice for small and medium businesses up to the large enterprise. It can provide outstanding uptime to keep business-critical applications and cloud deployments running safely. Ease of use and comprehensive systems management tools help make deployment easier. Outstanding reliability, availability, and serviceability (RAS) and high-efficiency design improve your business environment and help save operational costs.
Optimus® Product Family of SAS SSDs
Available in capacities of 100GB – 2TB, Optimus SAS SSDs from SanDisk provide
The Optimus SAS SSD portfolio has been designed to offer a range of performance and capacity solutions for a variety of applications that include mixed-use, read-intensive, and write-intensive workloads. Optimus SAS SSDs range from one to 45 full random drive writes per day (DWPD). The Optimus SAS SSD has a five-year warranty. For more information on Optimus SAS SSDs, refer to the product datasheet:
The Optimus SAS SSD product family is powered by SanDisk’s proprietary Guardian Technology Platform, a comprehensive suite of enterprise features comprised of FlashGuard™, DataGuard™ and EverGuard™ technologies.
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 the Optimus SAS SSD’s engineering, integration, and optimization to quickly build and deploy their next data warehouses.
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 technology eliminates the need to build summary tables, thus further reducing ETL run times.
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. The SQL Server Data Warehouse Fast Track eliminates the complexity of sizing servers with data warehouses by providing data consumption rates that properly balance 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
The following table shows the configuration details for the Optimus Ascend 5TB Fast Track Data Warehouse.
Server | Lenovo System x3650 M5 | |
Operating System | Microsoft Windows Server 2012 R2 Standard Edition | |
CPU | Intel Xeon E5-2620 v3 (single-socket) | |
Primary Data Drives | 8 x 400GB Optimus Ascend SAS SSDs from SanDisk (RAID 5) | |
RAID Controller | ServeRAID M5210 SAS/SATA Controller | |
OS Drives | 2 x 1TB SAS (Mirrored) | |
RAM | 128GB |
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 OS with default values, followed by service packs and update patches.
Windows Configuration – Power Settings
The High Performance plan was chosen to reduce CPU throttling, as shown below.
Optimus Ascend Configuration
Each Optimus Ascend SAS SSD was attached to the ServeRAID M5210 Controller and configured as a single Drive Group (RAID 5) set, with a single virtual drive presented to the Windows OS.
ServeRAID M5210 Controller
The ServeRAID M5210 SAS/SATA controller is a PCIe 3.0, half-size, half-height RAID controller based on the LSISAS3108 PCI Express-SAS/SATA I/O Processor chip.
The controller controls eight internal SAS/SATA ports through two SFF-8643 4i internal mini-SAS HD connectors. The controller integrates eight high-performance SAS/SATA PHY chips and a PCI Express bus master DMA core. Each of the eight PHYs is capable of 12.0 Gb/s SAS link rates and 6.0 Gb/s SATA III link rates.
Storage Configuration
The following configuration was chosen.
Slot | Device | Capacity | Mount Point | Allocation | Notes |
N/A | Optimus Ascend | 2.2TB | C:\mounthead\ | Data Files\TempDB\LOG | RAID 5 |
N/A | SAS HDD | 1.0TB | C:\ | OS | Mirrored |
UEFI Configuration
SQL Server Settings
SQL Server 2014 Configuration Parameters
Parameter | Setting | Description | ||
Memory Allocation | 118GB | This is the Fast Track-required value for a 2-socket, 1 TB database. Memory is deliberately constrained to enforce I/O pressure on the subsystem. | ||
Max Degree of Parallelism | 12 | 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 | None | No additional Trace Flags were used. |
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 | 5TB | 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 | 46 | Percentage throughput of this configuration in comparison to the FTDW reference configuration. The reference configuration value is 100, but this is achieved with both sockets populated. | ||
Column Store Relative Throughput | 62 | Percentage throughput of this configuration in comparison to the FTDW reference configuration. The reference configuration value is 100, but this is achieved with both sockets populated. | ||
Maximum User Data Capacity | 8TB | Calculated based on total disk capacity and allows 5:1 compression. Factors recommended free space and ignores the throughput limits that are applied to the Rated User Capacity. | ||
Measured Throughput (Q/Hr/TB) | 49 | Number of combined benchmark queries completed during the measurement interval. Normalized to the 1 TB database. |
Together, Lenovo and SanDisk dedicated hundreds of hours of testing to engineer this SQL Server DWFT solution to provide optimal reliability and performance. These series of tests pushed the Lenovo System x3650 M5 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 1.3GB/s, and high query rates in the Column Store configuration at 402 Q/Hr/TB.
The Lenovo System x3650 M5 with Optimus Ascend SAS SSD technology from SanDisk delivers superb performance, with the ability to host larger data warehouses or consolidate multiple data warehouses.
Lenovo System x3650 M5
Qty | SKU | Description | ||
1 | 5462AC1 | 6coreTopSeller: Lenovo System x3650 M5 | ||
1 | A1ML | Integrated Management Module Advanced Upgrade | ||
2 | A5EU | System x 750W High Efficiency Platinum AC Power Supply | ||
1 | A5GH | System x3650 M5 Rear 2x 2.5" HDD Kit (Independent RAID) | ||
1 | A483 | Populate and Boot From Rear Drives | ||
1 | A5EY | System Documentation and Software-US English | ||
1 | A5FV | System x Enterprise Slides Kit | ||
1 | A5EA | System x3650 M5 Planar | ||
8 | A5B7 | 16GB TruDDR4 Memory (2Rx4, 1.2V) PC4-17000 CL15 2133MHz LP RDIMM | ||
1 | A5FH | System x3650 M5 Agency Label GBM | ||
1 | A5FM | System x3650 M5 System Level Code | ||
1 | 9206 | No Preload Specify | ||
1 | A4VH | Lightpath LCD Op Panel | ||
1 | A5FZ | System x3650 M5 Riser Filler | ||
2 | A5V6 | System x3650 M5 System Fan Filler | ||
1 | ASQA | System x3650 M5 Rear 2x 2.5" HDD Label (Independent RAID-Riser1) | ||
2 | A2HP | Configuration ID 01 | ||
2 | A4EL | HDD Filler ASM GEN 3 Quad Filler | ||
1 | A5FT | System x3650 M5 Power Paddle Card | ||
1 | A5G1 | System x3650 M5 EIA Plate | ||
1 | A5H0 | 2U Bracket for Broadcom NetXtreme 2x10GbE BaseT Adapter | ||
1 | A5V4 | System x3650 M5 Right EIA | ||
1 | A5FC | System x3650 M5 WW Packaging | ||
1 | A5G4 | System x3650 M5 ODD Filler | ||
1 | A5ED | Intel Xeon Processor E5-2620 v3 6C 2.4GHz 15MB Cache 1866MHz 85W | ||
1 | 5977 | Select Storage devices - no configured RAID required | ||
1 | A5G6 | x3650 M5 8x 2.5" HS HDD Assembly Kit (Single RAID) | ||
1 | A3YZ | ServeRAID M5210 SAS/SATA Controller | ||
1 | A45W | ServeRAID M1215 SAS/SATA Controller | ||
1 | A5FD | System x3650 M5 2.5" Base without Power Supply | ||
1 | A5FY | System x3650 M5 2.5" ODD/LCD Light Path Bay | ||
2 | A4TL | 300GB 10K 6Gbps SAS 2.5" G3HS HDD | ||
8 | A4UB | 400GB SAS 2.5" MLC G3HS Enterprise SSD | ||
1 | A5G3 | System x3650 M5 2.5" ODD Bezel with LCD Light Path | ||
1 | A5GZ | Broadcom NetXtreme 2x10GbE BaseT Adapter | ||
1 | 5374CM1 | HIPO : Configuration Instruction | ||
1 | A5M2 | ServeRAID M1215 SAS/SATA Controller Upgrade Placement | ||
1 | A2HP | Configuration ID 01 | ||
1 | A2JX | Controller 01 | ||
1 | 5374CM1 | HIPO : Configuration Instruction | ||
1 | A46P | ServeRAID M5210 SAS/SATA Controller Placement | ||
1 | A2HP | Configuration ID 01 | ||
1 | A2JY | Controller 02 |