REFERENCE ARCHITECTURE
EMC XTREMIO FOR MICROSOFT SQL SERVER 2014
DATA WAREHOUSE FAST TRACK
EMC Solutions
March 2015
Copyright © 2015 EMC Corporation. All Rights Reserved.
Published March 2015
EMC believes the information in this publication is accurate as of its
publication date. The information is subject to change without notice.
The information in this publication is provided as is. EMC Corporation makes no
representations or warranties of any kind with respect to the information in this
publication, and specifically disclaims implied warranties of merchantability or
fitness for a particular purpose.
Use, copying, and distribution of any EMC software described in this
publication requires an applicable software license.
For the most up-to-date listing of EMC product names, see EMC Corporation
Trademarks on EMC.com.
All trademarks used herein are the property of their respective owners.
Part Number H13925
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
2
Table of contents
Reference architecture overview ......................................................................................................... 5
Document purpose .......................................................................................................................... 5
The business challenge .................................................................................................................... 5
Introduction to EMC XtremIO ............................................................................................................ 5
The technology................................................................................................................................. 6
Scope .............................................................................................................................................. 6
The audience ................................................................................................................................... 6
Solution architecture .......................................................................................................................... 7
Architecture diagram ........................................................................................................................ 7
Hardware resources ......................................................................................................................... 8
Software resources ......................................................................................................................... 8
Value proposition ............................................................................................................................... 9
Microsoft Data Warehouse Fast Track program ................................................................................. 9
SQL Server clustered columnstore index .......................................................................................... 9
Solution hardware............................................................................................................................ 9
Cisco UCS Rack Server ................................................................................................................... 10
XtremIO X-Brick .............................................................................................................................. 10
XtremIO management .................................................................................................................... 12
Sizing XtremIO ............................................................................................................................... 14
Storage layout configuration ..................................................................................................... 14
I/O throttle ..................................................................................................................................... 15
Queue depth .................................................................................................................................. 16
Microsoft MPIO .............................................................................................................................. 16
EMC PowerPath .............................................................................................................................. 17
Database configuration .................................................................................................................. 17
Performance overview .................................................................................................................... 18
SQL Server settings .......................................................................................................................... 19
Start-up options ............................................................................................................................. 19
SQL Server memory settings........................................................................................................... 19
Hyper-threading ............................................................................................................................. 19
SQL Server MAXDOP setting ........................................................................................................... 19
Resource Governor ......................................................................................................................... 20
Upgrading from Data Warehouse Fast Track 4.0 ................................................................................ 21
SQL Server 2014 Data Warehouse Fast Track metrics ..................................................................... 21
Certification and bill of materials ...................................................................................................... 22
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
3
20-TB certification .......................................................................................................................... 22
45-TB certification .......................................................................................................................... 23
Bill of materials .............................................................................................................................. 24
20-TB certification ..................................................................................................................... 24
45-TB certification ..................................................................................................................... 25
Conclusion ....................................................................................................................................... 27
References ....................................................................................................................................... 28
EMC documentation ....................................................................................................................... 28
Microsoft documentation ............................................................................................................... 28
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
4
Reference architecture overview
Document purpose This document describes the reference architecture of the EMC® Data Warehouse Fast
Track for Microsoft SQL Server 2014 solution, which uses the EMC XtremIOTM storage
system on Cisco UCS C-Series Rack Mount servers with Intel E5-4650 and E5-2650
processors.
The business
challenge
Customers require a scalable, efficient, and highly available data warehouse
infrastructure. Enterprise data warehouses provide the foundation for business
intelligence systems, which continue to grow dramatically as organizations realize
the importance of mining their accumulated data for key business insights. Massive
data growth is often accompanied by increased complexity, while data warehouse
configuration typically suffers from design principles that were originally intended for
online transaction processing (OLTP) systems.
The reference configurations presented in this document address the common
business challenges that data warehouses face today, and help customers design
and implement balanced configurations specifically for Microsoft SQL Server data
warehouse databases with the goal of providing a hardware-balanced approach and
predictable out-of-box performance.
Introduction to
EMC XtremIO
EMC XtremIO is an all-flash scale-out enterprise storage array that not only delivers
high performance and scalability, but also brings new levels of ease-of-use to storage
area network (SAN) storage. This next-generation storage system combines powerful
and flexible hardware with advanced efficiency, management, and protection
features to meet the demanding needs of today’s enterprises.
Significant XtremIO features include:

Performance—XtremIO supports very high input/output operations per
second (IOPS) with sub-millisecond latency. Performance is consistently
predictable and constant.

Data protection— XtremIO has its own data protection algorithm called
XtremIO Data Protection (XDP). XDP is dedicated to fast rebuilds and all-round
protection and performs better than the traditional RAID types. XDP places
data in any location on any SSD, without considering how to keep data
contiguous to avoid disk drive head seeks. It reads data back in highly
efficient ways with no adverse impact on XtremIO all‐flash architecture.

Thin provisioning—All volumes in the XtremIO All-Flash Array are thinprovisioned, allowing capacity purchases to be deferred from the time of
provisioning until the data is written to the array.

SSD longevity—XtremIO technologies ensure the storage system will last for
at least seven years under constant, full-speed write workloads.

Inline deduplication—The XtremIO All-Flash Array automatically deduplicates
data as it enters the system, which reduces the amount of data written to
flash, extending the flash lifetime and improving application response time.
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
5
The technology

Inline data compression—XtremIO automatically compresses data after all
duplications have been removed. Compression saves storage capacity by
storing data blocks in the most efficient manner.

XtremIO snapshots—XtremIO provides the industry’s first writable snapshot
technology that is space-efficient for both data and metadata. Free from
limitations of performance, features, topology, or capacity, XtremIO
snapshots can instantly:

Create entire application development and test environments where every
engineer gets a personal sandbox

Enable high-performance analytics and reporting without any brute force
data movement

Deliver near-continuous data protection (CDP) capabilities for data
protection
This solution demonstrates how to use XtremIO to provide the storage resources for a
robust Microsoft data warehouse environment.
Data Warehouse Fast Track for Microsoft SQL Server 2014 provides a framework that
enables customers to select a reference architecture that has been designed, tested,
and validated by hardware vendors using Microsoft guidance and tools. It is designed
to enable organizations to quickly and reliably deploy a total system solution stack,
including server, storage, and connectivity, for data warehouses based on Microsoft
SQL Server 2014. This approach helps dramatically reduce the burden on end users
in selecting, sizing, and testing the combination of products and technologies while
minimizing the risk of failures.
The resulting solution is a balanced and optimized hardware and software design for
SQL Server data warehouse deployments with a configuration that can:
Scope

Eliminate the complexity of data warehouse design

Reduce total cost of ownership (TCO)

Offer better performance to users
The purpose of this reference architecture is to build and demonstrate the
functionality, performance, and scalability of the Data Warehouse Fast Track for
Microsoft SQL Server 2014 solution as enabled by XtremIO and Cisco Unified
Computing System (UCS) servers.
The reference architecture validates the performance of the solution and provides
guidelines for building similar solutions.
This document is limited to the main repository of the data warehouse and the
balanced configuration of the repository. It is not a comprehensive guide to every
aspect of the solution.
The audience
This document is intended for customers, partners, solution architects, storage
administrators, and database administrators who evaluate, plan, and deploy
balanced data warehouse solutions.
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
6
Solution architecture
Architecture
diagram
This Data Warehouse Fast Track for Microsoft SQL Server 2014 reference architecture
uses the XtremIO storage system and the Cisco UCS C-Series Rack Server. The
configurations are designed to optimize for data warehouse workloads and are tested
by EMC and rated by Microsoft for up to 45 TB of user data capacity.
Figure 1 depicts the overall physical architecture of the solution.
Figure 1.
Solution architecture
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
7
Hardware
resources
Table 1 lists the hardware used in this solution.
Table 1.
Solution hardware
Hardware
Software
resources
Quantity
45-TB certification
20-TB certification
EMC XtremIO All-Flash Array
2
1
Cisco UCS server
1
1
QLogic Fibre Channel controllers
4
2
Fibre Channel multimode cables with
single fiber (SF) connectors
8
4
Table 2 lists the software used in this solution.
Table 2.
Solution software
Software
Configuration
Microsoft Windows
Microsoft Windows Server 2012 R2 Standard Edition
Microsoft SQL Server
Microsoft SQL Server 2014 Enterprise Edition
Fast Track testing
Microsoft Reference Point Service version 5
EMC PowerPath
EMC PowerPath version 5.7 SP2
EMC XtremIO XMS
XtremIO 3.0.1
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
8
Value proposition
Microsoft Data
Warehouse Fast
Track program
Optimized for common data warehouse, decision support, and business analytics
deployments based on Microsoft SQL Server, the Data Warehouse Fast Track for
Microsoft SQL Server 2014 program enables organizations to deploy tested
configurations with proven technologies that offer balanced solution stacks.
Three principles constitute the value proposition for Data Warehouse Fast Track:
SQL Server
clustered
columnstore index
Solution
hardware

Predetermined balance across key system components—this helps minimize
the risk of overspending for CPU or storage resources.

Predictable out-of-the-box performance—the Fast Track configuration matches
SQL Server application capacity for a target workload.

Workload-centricity—the Fast Track reference architecture is aligned
specifically with the corresponding data warehouse workload. It is not a onesize-fits-all scenario.
The Data Warehouse Fast Track program for SQL Server 2014 takes full advantage of
the clustered columnstore index (CCI) technology in SQL Server. CCI stores data in
columnar storage, enabling ten times as much data compression while speeding up
the query performance with batch-mode processing as follows:

Provides superior data compression, reducing storage and I/O bandwidth
requirement as only columns referenced in the query need to be brought into
memory

Reduces the need for secondary indexes, simplifies the overhead on
incremental data load, and enables customers to load data in the
maintenance window

Executes ad hoc, complex analytic queries efficiently because it uses
predicate pushdown and partition elimination to reduce the data processing
of large data sets
The solution hardware for this reference architecture includes a small and a mid-size
certification for SQL Server Data Warehouse Fast Track. The small-size reference
architecture is certified with 20 TB of rated user data capacity up to a maximum of 60
TB. The mid-size reference architecture is certified with 45 TB of user capacity up to a
maximum user data capacity of 105 TB. Table 3 shows the certification requirements.
Table 3.
Solution hardware for Data Warehouse Fast Track reference architecture
Data Warehouse
Fast Track
reference architecture
Cisco server
XtremIO All-Flash Arrays
EMC XtremIO 20 TB
solution
Cisco UCS C240 M3 server
with Intel Xeon Processor
E5-2620 CPUs
EMC XtremIO single X-Brick
storage array with 25x400 GB SSD
EMC XtremIO 45 TB
solution
Cisco UCS C420 M3 server
with Intel Xeon Processor
E5-4650 CPUs
EMC XtremIO dual X-Brick
storage array with 50x800 GB SSD
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
9
Cisco UCS
Rack Server
Cisco UCS C-Series Rack Servers deliver unified computing in an industry-standard
form to reduce TCO and increase agility. Each product addresses varying workload
challenges through a balance of processing, memory, I/O, and internal storage
resources.
The 45-TB certification uses the Cisco UCS C420 M3 Rack Server, which is a high
density, four-socket, two-rack unit (2RU) server designed for compute, I/O, storage,
and memory-intensive stand-alone and virtualized applications. The addition of the
Intel Xeon processor E5-4600 product family delivers an optimal combination of
performance, flexibility, and efficiency gains.
The server for 20-TB certification is the Cisco UCS C240 M3 Rack Server. It extends the
capabilities of the 2 RU form factor and is designed for performance and
expandability over a wide range of storage-intensive infrastructure workloads,
ranging from big data to collaboration.
Intel Xeon processors are at the heart of a server system that delivers outstanding
scalability and performance to support the new business intelligence capabilities
available in Microsoft SQL Server 2014. These enhancements enable large-scale
consolidation efforts while serving demanding data-intensive workloads.
XtremIO X-Brick
Each X‐Brick is configured as an Active/Active, highly available, clustered storage
array without any single point of failure. The flash drive capacity can be 400 GB or
800 GB. The physical user capacity for a single-brick cluster before data reduction is
about 7.5 TB with a 400 GB flash drive, and about 30.5 TB with an 800 GB flash drive.
Every volume is thin‐provisioned. With deduplication and compression, the effective
logical capacity usable by hosts is much larger, and the SQL Server database data
reduction rate was maintained at close to 2:1 in our testing. As a result of
compression and snapshot functionality, a single X-Brick XtremIO All-Flash Array can
potentially host data typically requiring more than six times as much physical storage
capacity.
XtremIO offers performance, capacity, protection, and the best TCO to meet
diversified business requirements. The boosted, sustained read-and-write
bandwidth it offers is crucial for typical data warehouse deployment. Figure 2 shows
the XtremIO X-Bricks in a one-, two,- and four-unit configuration with two Cisco
servers: the Cisco UCS C420 M3 and the Cisco UCS C240 M3.
Figure 2.
XtremIO X-Brick scaling units with two Cisco servers
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
10
Table 4 and Table 5 list the hardware configuration for both the small and the midsize solution.
Table 4.
Hardware configuration for XtremIO 20-TB reference architecture
Hardware
Components
Quantity/description
Server
Cisco UCS Server
UCS C240 M3
CPU
2 x 6 Core Intel Xeon processors model E52620
(2.0 GHz)
Number of cores
12
PCI-E slots
2 x 8 PCIe3.0 or 2 x 16 PCIe3.0
Internal drives
2 x 146 GB SAS disk drives
Storage controller
1 LSI MegaRAID SAS 9261-8i
Network adapters
2 x Gigabit Ethernet LAN-on-motherboard (LOM)
ports, 2 x 10 Gigabit Ethernet ports, and 2 x
dedicated out-of-band (OOB) management ports
RAM
128 GB PC2-5300 Fully Buffered dual in-line
memory modules (DIMMs) (FBD)
Memory slot
32 GB DDR3 DIMMs with 64 DIMM slots
EMC XtremIO
Single X-Brick XtremIO All-Flash Storage Array
Host bus adapters
2 x QLogic 2562 dual-port 8 Gb HBA
Disk drives
25 x 400 GB SSD
Data protection
XDP built-in data protection
Protocol
Fibre Channel
Storage
Network
connectivity
Table 5.
Hardware configuration for XtremIO 45-TB reference architecture
Components
Server
Quantity/Description
Cisco UCS Server
UCS C420 M3
CPU
4 x 8 Core Intel Xeon processors model E5-4670
(2.7 GHz)
Number of cores
32
PCI-E slots
10
Internal drives
2 x 300 GB SAS disk drives
Storage controller
1 LSI MegaRAID SAS 9261-8i
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
11
Components
Quantity/Description
Storage
Connectivity
Network Adapters
2 x Gigabit Ethernet LAN-on-motherboard (LOM)
ports, 2 x 10 Gigabit Ethernet ports, and 2 x
dedicated out-of-band (OOB) management ports
RAM
384 GB DDR3 RDIMMs
Memory Slot
32 GB DDR3 DIMMs with 64 DIMM slots
EMC XtremIO
Dual X-Brick XtremIO cluster
Host bus adapters
4 x QLogic 2562 dual-port 8 Gb HBA
Disk drives
50 x 800 GB SSD
Data protection
XDP built-in data protection
Protocol
Fibre Channel (FC)
Table 6 lists the software configuration for both the small and the mid-size solution.
Table 6.
XtremIO
management
Software configuration for EMC XtremIO reference architecture
Software
Configuration
Windows
Microsoft Windows Server 2012 R2 Standard Edition
SQL Server
Microsoft SQL Server 2014 Enterprise Edition
Fast Track testing
Microsoft Reference Point Service Version 5.0
EMC PowerPath
EMC PowerPath version 5.7 SP1
XtremIO
XtremIO 3.0
XtremIO Management Server (XMS) is the management station for an XtremIO system
that can be physical or virtual and is accessed through the XMS GUI or CLI interface.
Figure 3 shows a sample XMS dashboard summary page. This page provides
administrators with a wealth of information about connected storage systems,
including storage panel overall efficiency, physical and logical capacity, and volume
performance detail.
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
12
Figure 3.
XtremIO Storage Management Application dashboard
Figure 4 shows the XMS storage panel, which provides information about the physical
and logical volume as well as the deduplication and compression ratio. The panel
shows that XtremIO achieves about 1.3:1 compression on top of the SQL Server
compression.
Figure 4.
XtremIO Management Application dashboard storage panel
The benefits of the XtremIO deduplication and compression features include:

Inline data deduplication—The XtremIO All-Flash Storage Array automatically
deduplicates data as it enters the system, which reduces the amount of data
written to flash, thereby extending the flash lifetime and improving
application response time.

Inline compression— XtremIO automatically compresses data after all
duplications have been removed, which ensures that the compression is
performed only for unique data blocks. Data compression further reduces the
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
13
data footprint by eliminating data redundancy within the binary level of each
data block.
Sizing XtremIO
Sizing the system’s storage capacity is relatively simple. Each 10 TB X-Brick can hold
up to 7.5 TB of physical storage. Thanks to the deduplication, compression and
snapshot features, the maximum logical capacity for a single X-Brick system with 2:1
compression ratio could range from 117 TB to 138 TB.
All volumes in XtremIO All-Flash Arrays are thin-provisioned. After logging in to the
XMS Web GUI, create a volume and define its name and size, as shown in Figure 5.
Figure 5.
Create volumes in XtremIO All-Flash Array
XtremIO allows for expansion of a volume. After expanding a volume, the host must
rescan the LUN for changes and query the volume to show the new size. Thanks to the
management and design simplicity, customers do not need to create RAID groups or
assign hot spares.
Storage layout configuration
Table 7.
Storage layout for 20 TB certification
Volume
number
Volume
name
Capacity
Mount point
Allocation
1
FTDATA1
2 TB
C:\Mount\FTDATA1
Data files and TempDB files
2
FTDATA2
2 TB
C:\Mount\FTDATA2
Data files and TempDB files
3
FTDATA3
2 TB
C:\Mount\FTDATA3
Data files and TempDB files
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
14
Volume
number
Volume
name
Capacity
Mount point
Allocation
4
FTDATA4
2 TB
C:\Mount\FTDATA4
Data files and TempDB files
5
FTDATA5
2 TB
C:\Mount\FTDATA5
Data files and TempDB files
6
FTDATA6
2 TB
C:\Mount\FTDATA6
Data files and TempDB files
7
FTDATA7
2 TB
C:\Mount\FTDATA7
Data files and TempDB files
8
FTDATA8
2 TB
C:\Mount\FTDATA8
Data files and TempDB files
9
LOG
500 GB
C:\Mount\Log
Log files
10
Staging
3 TB
c:\Mount\Staging
Staging files
Table 8.
I/O throttle
Storage layout for 45 TB certification
Volume
number
Volume
name
Capacity
Mount point
Allocation
1
FTDATA1
3 TB
C:\Mount\FTDATA1
Data files and TempDB files
2
FTDATA2
3 TB
C:\Mount\FTDATA2
Data files and TempDB files
3
FTDATA3
3 TB
C:\Mount\FTDATA3
Data files and TempDB files
4
FTDATA4
3 TB
C:\Mount\FTDATA4
Data files and TempDB files
5
FTDATA5
3 TB
C:\Mount\FTDATA5
Data files and TempDB files
6
FTDATA6
3 TB
C:\Mount\FTDATA6
Data files and TempDB files
7
FTDATA7
3TB
C:\Mount\FTDATA7
Data files and TempDB files
8
FTDATA8
3 TB
C:\Mount\FTDATA8
Data files and TempDB files
9
LOG
1 TB
C:\Mount\Log
Log files
10
Staging
4 TB
c:\Mount\Staging
Staging files
To optimize performance to extreme levels, you must configure host access to the
XtremIO All-Flash storage array to enable higher I/O throughput. It is important to
choose the UCS server’s highest setting to maximize the I/O throttle for the vHBA
Fibre Channel port. In the tested solution, we1 set the I/O throttle counts for both the
45-TB and the 20-TB certification to 1024, as shown in Figure 7.
1
In this Reference Architecture, "we" refers to the EMC Solutions engineering team that
validated the solution.
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
15
Figure 6.
Queue depth
When using Windows with XtremIO, for optimal performance we recommend
adjusting the queue depth and execution throttle of the Fibre Channel HBA to the
maximum value. The maximum queue depth value is currently 256 for QLogic HBAs.
You can adjust this value in the Windows Registry, as shown in Figure 8.
Figure 7.
Microsoft MPIO
Set the I/O throttle count
Adjust the queue depth
XtremIO supports native Microsoft Multipath I/O (MPIO), which must be enabled
through Windows Server Manager. To send I/O down the path with the fewest
currently outstanding I/O requests, configure it for the lowest queue depth policy, as
shown in Figure 8. Note that exactly eight blank spaces are required before the end
quote in the first command. This command will automatically reboot the host without
prompting. Refer to the XtremIO Host Configuration Guide for more information.
Figure 8.
Configure the lowest queue depth policy for XtremIO
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
16
EMC PowerPath
XtremIO supports EMC PowerPath multipathing with PowerPath 5.7 SP2 or later.
PowerPath processes XtremIO devices as native devices. We used PowerPath in our
testing to maximize multipathing and load-balancing, as shown in Figure 9.
Figure 9.
Database
configuration
PowerPath multipathing
We performed the benchmark testing for the reference architecture with the TPC-Hlike database and a sequential-read workload simulation tool known as Reference
Point Service.
Microsoft’s Fast Track Data Warehouse Reference Guide for SQL Server 2014 states
that the test database size should be based on the number of sockets on the server.
For 45-TB certification, we used a four-socket server with a 2 TB TPC-H testing
database, and for 20-TB certification we used a two-socket server with a 1 TB TPC-H
testing database. The Data Warehouse Fast Track testing tool constrains the database
size and SQL Server maximum memory based on the number of sockets in the
reference implementation being tested. For a two-socket server, the testing database
is 1 TB with an SQL Server maximum memory of 118 GB, while a four-socket server
should be combined with a 2 TB testing database with a maximum SQL Server
memory of 236 GB . These constraints allow precise certification metrics and accurate
characterization of the reference implementation.
The test database uses a primary database file group with additional secondary file
groups that represent the seven partitions. Table 9 lists the details.
Table 9.
Test database file groups
File group
Files
Notes
Primary
1
Contains the master database file (MDF)
Part_ci1FG
12
1 file on each LUN
Part_ci2FG
12
2 files on each LUN
Part_ci3FG
12
3 files on each LUN
Part_ci4FG
12
4 files on each LUN
Part_ci5FG
12
5 files on each LUN
Part_ci6FG
12
6 files on each LUN
Part_ci7FG
12
7 files on each LUN
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
17
Performance
overview
The testing consisted of a minimum of 12 I/O-intensive and CPU-intensive
performance runs against the row store and the column store. The performance
bandwidth requirements defined in Table 10 are output from the simulation tool.
Table 10.
Database configuration certification performance bandwidth requirements
Performance bandwidth
45 TB
20 TB
Row store rated I/O (MB/s)
4,372
2,235
Column store rated I/O (queries/Hr/TB)
1,337
574
On the storage side, with a mixed data warehouse workload of 256 KB and 512 KB
and a 90% read-10% write combination, the maximum throughput could reach 3,000
MB/s for a single X-Brick system, as shown in Figure 10, and 5,300 MB/s for a dual XBrick cluster, as shown in Figure 11.
Figure 10.
20 TB performance bandwidth for SQL Server 2014
Figure 11.
45 TB performance bandwidth for SQL Server 2014
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
18
SQL Server settings
Start-up options
Configure the following SQL Server startup parameters:
1.
Add -E to the start-up options. This increases the number of contiguous
extents in each file that are allocated to a database table as it grows, thereby
improving sequential disk access.
2.
Add -T1117 to the start-up options. The trace flag ensures even growth of all
files in a file group, which in turn ensures maximum physical I/O
performance.
SQL Server
memory settings
SQL Server 2014 best practice is to allocate no more than 92 percent of the total
available RAM to SQL Server. If additional applications will share the server, decrease
the amount of maximum SQL Server memory accordingly.
Hyper-threading
The processors in the UCS C-Series server use Intel Hyper-Threading Technology,
which allows the server to use more logical CPU cores than are physically available in
the system. When higher intensity workloads use a large number of parallel queries,
the hyper-threading technology allows SQL Server to oversubscribe the amount of
CPU and memory resources given to queries during run time. For example, in 45-TB
certification configuration, the Cisco UCS C420 server has 32 physical CPU cores, but
SQL Server could allocate up to 64 logical CPU cores to queries after hyper-threading
is enabled.
SQL Server
MAXDOP setting
SQL Server can dynamically allocate SQL threads with hyper-threading technology.
Maximum degree of parallelism (MAXDOP) controls the number of logical cores that
SQL Server uses for the parallel execution of a query. In our testing we used the
following MAXDOP settings:

20-TB certification: 12 for row store and 24 for column store

45-TB certification: 32 for row store and 64 for column store to provide the
best scan rates for the respective runs without requiring use of 100 percent of
the processors
Figure 12 shows a MAXDOP parameter setting of 32 in 45-TB certification testing.
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
19
Figure 12.
SQL Server MAXDOP setup
Resource Governor You can use SQL Server Resource Governor to reduce the maximum memory
consumed by each query, setting the constraint options to provide the balance you
need:

Lower constraints provide higher performance for individual queries.

Increased constraints guarantee the number of queries that can run
concurrently.
The default setting for SQL Server 2014 is 25 percent of SQL Server memory resources
for each session. In our testing we used the following Resource Governor memory
grant settings:

45-TB certification: 6 percent for row store and 25 percent for column store
runs

20-TB certification: 12 percent for both row store and column store runs
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
20
Upgrading from Data Warehouse Fast Track 4.0
No new hardware is required to upgrade from Data Warehouse Fast Track 4.0
for SQL Server 2012 to Data Warehouse Fast Track for SQL Server 2014. Simply
upgrade the software to SQL Server 2014. The throughput remains the same.
SQL Server 2014
Data Warehouse
Fast Track metrics
Data Warehouse Fast Track for SQL Server 2014 certification uses the following
primary metrics for capacity evaluation:

Rated user data capacity—Calculated based on the row store relative
throughput, the column store relative throughput, available storage, and the
physical system memory. The calculation is based on an assumed
compression ratio of 5:1.

Maximum user data capacity—Calculated based on the total disk capacity of
all disks allocated to primary data storage. A compression ratio of 5:1 is
assumed.

Row store relative throughput—Calculated as a percentage ratio of the row
store throughput to the row store throughput of the validated sample Data
Warehouse Fast Track reference configuration (a two-socket system with
25 TB of rated user data capacity)

Column store relative throughput—Calculated as a percentage ratio of the
column store throughput to the column store throughput of the Data
Warehouse Fast Track reference architecture.
The testing tool for SQL Server 2014 Data Warehouse Fast Track certification is
designed based on a compression ratio of 5:1 because of the page compression
feature introduced in SQL Server 2008, which enables data warehouse workloads
to achieve a typical data compression of two times to five times while keeping the
data in row structures. Customers should verify that the reference architecture
they are deploying contains enough storage to accommodate their data needs.
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
21
Certification and bill of materials
20-TB certification
Figure 13 shows the Data Warehouse Fast Track certification for the small solution.
Figure 13.
20-TB certification
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
22
45-TB certification
Figure 14 shows the Data Warehouse Fast Track certification for the mid-size solution.
Figure 14.
45-TB certification
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
23
Bill of materials
The bill of materials is a list of the part numbers and stock-keeping units (SKUs) in
the solution as well as the quantities needed to assemble it.
20-TB certification
Table 11 lists the server components in the small solution.
Table 11.
Server components
Product
SKU
Quantity
UCS C240 M3 Rack Server (without CPU, MRB, PSU)
C240-BASE-2646
1
2.0 GHz E5-2620
UCS-CPU-E52620
2
2 X 8 GB DDR3-1333-MHz RDIMM/PC3-10600/dual
rank/x2/1.35v
UCS-MR-2X082RX-C
8
8-drive backplane with expander for C-Series
UCSC-DBKP-08E
2
300 GB 6 Gb SAS 10K RPM SFF HDD/hot plug/drive
sled mounted
A03-D300GA2
4
QLogic QLE 2562-HBAA 2 port 8 Gb SFP+ Copper
N2XX-AQPCI01
4
LSI 6G MegaRAID 9261-8i card (RAID 0,1,5,6,10,60) 512WC
R2XX-PL003
1
1200W 2u power supply for UCS
UCSC-PSU2-1200
2
Power cord, 200/240V 6A North America
CAB-N5K6A-NA
2
Cable Management Arm - 2u for C-Series
UCSC-CMA-0002
1
2U Rail Kit for UCS C-Series servers
UCSC-RAIL-2U
1
16 GB SD card module for UCS Servers
UCS-SD-16G
1
Memory kit for UCS-MR-2X082RX-C
UCS-MKIT-082RX-C
16
CPU heat sink for UCS C240 M3 Rack Server
UCSC-HS-01-C240
2
HDD slot blanking panel for UCS B-Series Blade
Servers
N20-BBLKD
12
Full height PCIe filler for C-Series
UCSC-PCIF-01F
1
.79m SAS RAID Cable for C240
UCSC-RC-P8M-C240
2
Onsite 24X7X4 UCS C240 M3 Rack Server
CON-OSP-C240-M3
1
Table 12 lists the storage components in the small solution.
Table 12.
Storage components
Storage
Model number
Quantity
XtremIO virtual rack
X02-VRACK-FD
1
XtremIO-25*400 GB flash drives with OE software
X02-D24-400F
1
XtremIO X-Brick-1 configuration kit
X02-KIT01-FD
1
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
24
Storage
Model number
Quantity
XtremIO storage controller with tag
X02-CTRL-TAGF
1
XtremIO storage controller
X02-CTRL-F
1
XtremIO - UPS 220V
X02-UPS-220F
2
45-TB certification
Table 13 lists the server components in the mid-size solution.
Table 13.
Server components
Server
Part number
Quantity
UCS C420 M3 Rack SVR without CPU, memory, HDD, or
PCIe
UCSC-BASE-M3C420
1
2.7 GHz E5-4650 130W 10C CPU / 30M cache
UCS-CPU-E54670
4
2 X 8 GB DDR3-1333-MHz RDIMM/PC3-10600/dual
rank/x2/1.35v
UCS-MR-2X082RX-C
16
LSI controller 9260-8i
RC420-PL001
1
600 GB 6 Gb SAS 10K RPM SFF HDD/hot plug/drive
sled mounted
A03-D300GA2
2
North America, NEMA L6-20 250V/20A plugIEC320/C13 receptacle
CAB-AC-250V/13A
4
Memory kit for UCS-MR-2X082RX-C
UCS-MKIT-082RX-C
32
Cable management arm for C420 M3
RC420-CBLARM
1
Rail kit for the UCS C420 M3 rack server
RC420-SLDRAIL
1
Memory riser board for C420 M3 server only
UCSC-MRB-002C420
8
UCS 2.5 inch HDD blanking panel
N20-BBLKD
10
CPU heat sink for UCS C420 rack server
RC420-BHTS1
4
1200W power supply unit for C-series C420 M3
RC420-PSU2-1200W
2
QLogic QLE 2562-HBA 2port 8Gb SFP+ Copper
N2XX-AQPCI01
4
Table 14 lists the storage components in the mid-size solution.
Table 14.
Storage components
Storage
Model number
Quantity
XtremIO virtual rack
X02-VRACK-FD
1
XtremIO -25*800 GB flash drives OE sw
X02-D24-800F
2
XtremIO X-Brick 2 configuration kit
X02-KIT02-FD
1
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
25
Storage
Model number
Quantity
XtremIO X-Brick 1 configuration kit
X02-KIT01-FD
1
XtremIO-IB switch
X02-IBSW-F
2
XtremIO- storage controller with tag
X02-CTRL-TAGF
1
XtremIO- storage controller
X02-CTRL-F
3
XtremIO- UPS 220V
X02-UPS-220F
2
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
26
Conclusion
The Microsoft SQL Server Fast Track architecture presents a balanced configuration of
processor core and disk performance capabilities optimized for sequential
workloads. The reference configuration presented in this document underwent
hundreds of hours of testing to provide the optimal and most reliable configuration
for SQL Server 2014 Data Warehouse Fast Track using Cisco UCS server and EMC
XtremIO. With a rated user capacity of 20 TB and 45 TB respectively, these two
solutions deliver the best mix of performance and data capacity, offering simplicity,
reliability, scalability, and a low TCO for enterprise-level data warehouse solutions.
Contact EMC or Microsoft today to learn if this offering is right for you.
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
27
References
EMC
documentation
Microsoft
documentation
The following documents, located on emc.com, provide more information:

EMC XtremIO white papers and data sheets

Introduction to EMC XtremIO Storage Array white paper
The following document, located on msdn.microsoft.com, provides more information
about the Microsoft SQL Server Data Warehouse Fast Track solution:

Data Warehouse Fast Track Reference Guide for SQL Server 2014
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track
Reference Architecture
28