Dell POWEREDGE R720XD User Manual

Database Solutions Engineering

Anthony Fernandez

Mayura Deshmukh

May 2012

Dell SMB Reference Configuration for

Microsoft® SQL Server® 2012 Fast Track

Data Warehouse on PowerEdge R720xd

This whitepaper describes the Dell Microsoft SQL Server Fast Track reference architecture configuration and performance details

Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd

This document is for informational purposes only and may contain typographical errors and technical inaccuracies. The content is provided as is, without express or implied warranties of any kind.

© 2012 Dell Inc. All rights reserved. Dell and its affiliates cannot be responsible for errors or omissions in typography or photography. Dell, the Dell logo, and PowerEdge are trademarks of Dell Inc. Intel and Xeon are registered trademarks of Intel Corporation in the U.S. and other countries. Microsoft, Windows, and Windows Server are either trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Other trademarks and trade names may be used in this document to refer to either the entities claiming the marks and names or their products. Dell disclaims proprietary interest in the marks and names of others.

May 2012 | Rev 1.0

Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd

Contents

Introduction ............................................................................................................. 4

Microsoft SQL Server Fast Track Data Warehouse ................................................................ 4

Dell Fast Track Data Warehouse Reference Architecture ....................................................... 4

Hardware Component Architecture .............................................................................. 5

Internal Storage Controller (PERC H710P Mini) Settings ...................................................... 7

Application Configuration ........................................................................................ 10

Performance Benchmarking ......................................................................................... 11

Baseline Hardware Characterization using Synthetic I/O ................................................... 11

Fast Track Database Validation ................................................................................. 13

Fast Track Database Validation with Column Store Index (CSI) ........................................... 14

Reference Architecture Performance Details ................................................................. 14

Conclusion ............................................................................................................. 15

References ............................................................................................................. 16

Tables

Table 1. Dell Fast Track Reference Architecture Solution Details ........................................... 4

Table 2. Tested Dell Fast Track Reference Architecture Component Details .............................. 5

Table 3. Mount Point Naming and the Storage Enclosure Mapping ........................................... 9

Table 4. Performance Metrics ..................................................................................... 14

Figures

Figure 1. Proposed Dell Fast Track Reference Architecture.................................................... 5

Figure 2. Memory Slot Locations .................................................................................... 6

Figure 3. Virtual Disk Settings ....................................................................................... 7

Figure 4. Internal Storage Controller Settings .................................................................... 8

Figure 5. RAID Configuration ......................................................................................... 8

Figure 6. Storage System Components ........................................................................... 10

Figure 7. SQLIO Line Rate Test from Cache (Small File) ...................................................... 12

Figure 8. SQLIO Real Rate Test from Disk (Large File) ........................................................ 13

Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on

Solution Description

Configuration ID

Dell Fast Track 4.0 Configuration PowerEdge R720xd

2209618

PowerEdge R720xd

Introduction

A data warehouse is a large store of data accumulated from a wide range of sources. The stored data is analyzed for trend analysis, business intelligence reporting, and various types of predictive analysis. With today’s never ending data growth and complexity, it is becoming a tedious job for customers to balance capacity and performance within the data warehouse system. Growing data volumes and loading challenges, OLAP query complexity, and number of users are causing response times to increase. IT executives are looking for solutions that offer lower cost, easier management, and better performance.

There are many challenges in designing a database configuration for OLAP workloads. One is ensuring an optimal balance of I/O, storage, memory and processing power.

Dell™ and Microsoft® jointly developed guidelines and design principles to assist customers in designing

and implementing a balanced configuration specifically for Microsoft SQL Server® data warehouse workloads to achieve “out-of-box” scalable performance.

This white paper describes the architecture design principles needed to achieve a balanced configuration for the Dell PowerEdge™ R720xd server using the Microsoft Fast Track Data Warehouse

4.0 guidelines.

Microsoft SQL Server Fast Track Data Warehouse

In order to overcome the limitations of traditional data warehouse systems, Microsoft has developed a cost-effective solution that optimally balances the hardware and software capabilities of the system. It provides an easy to deploy data warehouse infrastructure by mainly focusing on storage tuning and database layout. Fast Track Data Warehouse (FTDW) implements data warehouse solutions differently. As most data warehouse queries scan large volumes of data, FTDW designs are optimized for sequential scans and reads. These methodologies yield performance much better than that of traditional data warehousing systems. Based on this, Dell has developed a reference guide that helps customers implement FTDW on Dell hardware.

Dell Fast Track Data Warehouse Reference Architecture

In order to optimize data warehouse stack component performance, each layer must be properly tuned. Table 1 lists the Proposed Reference architecture along with the assigned Solution IDs. The following sections explain the tuning of selected hardware and software.

Table 1. Dell Fast Track Reference Architecture Solution Details

Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on

Component

Details

Server

PowerEdge R720xd

CPU

(1) Intel® Xeon® E5-2643 CPU @3.3GHz (HT Enabled)

Number of sockets used

Total Number of CPU Cores

Memory

96GB RAM (4 X 16GB DDR3 DIMMs @1333MHz

4 X 8GB DDR3 DIMMs @1333MHz)

Internal Hard Drives

22x 300GB 10K 2.5” SAS (18 Data, 4 Logs)

2x 600GB 10K 2.5” SAS (2 Hot Spares)

2x 600GB 10K 2.5” SAS (2 drives OS) Rear Bay

Operating System

Microsoft Windows® 2008 R2 SP1 Enterprise Edition

Database Software

Microsoft SQL Server 2012 Enterprise Edition

PowerEdge R720xd

Hardware Component Architecture

Redundant and robust tests have been conducted on Dell’s PowerEdge servers to determine best practices and guidelines for building a balanced FTDW system.

Figure 1. Proposed Dell Fast Track Reference Architecture

Table 2. Tested Dell Fast Track Reference Architecture Component Details

+ 11 hidden pages