emc xtremio for microsoft sql server 2014 data ... xtremio for microsoft sql server 2014 data...
Post on 08-Mar-2018
230 Views
Preview:
TRANSCRIPT
REFERENCE ARCHITECTURE
EMC Solutions
March 2015
EMC XTREMIO FOR MICROSOFT SQL SERVER 2014
DATA WAREHOUSE FAST TRACK
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
2
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
3 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
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
4
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
5 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
Reference architecture overview
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.
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.
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 thin-provisioned, 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.
Document purpose
The business challenge
Introduction to EMC XtremIO
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
6
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:
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.
This document is intended for customers, partners, solution architects, storage administrators, and database administrators who evaluate, plan, and deploy balanced data warehouse solutions.
The technology
Scope
The audience
7 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
Solution architecture
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
Architecture diagram
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
8
Table 1 lists the hardware used in this solution.
Table 1. Solution hardware
Hardware 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
Hardware resources
Software resources
9 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
Value proposition 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:
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 one-size-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
Microsoft Data Warehouse Fast Track program
SQL Server clustered columnstore index
Solution hardware
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
10
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.
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
Cisco UCS Rack Server
XtremIO X-Brick
11 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
Table 4 and Table 5 list the hardware configuration for both the small and the mid-size 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
Storage 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
Network connectivity
Protocol Fibre Channel
Table 5. Hardware configuration for XtremIO 45-TB reference architecture
Components Quantity/Description
Server 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
12
Components Quantity/Description
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
Storage 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
Connectivity Protocol Fibre Channel (FC)
Table 6 lists the software configuration for both the small and the mid-size solution.
Table 6. 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.
XtremIO management
13 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
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
14
data footprint by eliminating data redundancy within the binary level of each data block.
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
Sizing XtremIO
15 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
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. 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.
I/O throttle
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
16
Figure 6. Set the I/O throttle count
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. 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
Queue depth
Microsoft MPIO
17 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
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. PowerPath multipathing
We performed the benchmark testing for the reference architecture with the TPC-H-like 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 PowerPath
Database configuration
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
18
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 X-Brick 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
Performance overview
19 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
SQL Server settings
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 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.
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 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.
Start-up options
SQL Server memory settings
Hyper-threading
SQL Server MAXDOP setting
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
20
Figure 12. SQL Server MAXDOP setup
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
Resource Governor
21 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
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.
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.
SQL Server 2014 Data Warehouse Fast Track metrics
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
22
Certification and bill of materials Figure 13 shows the Data Warehouse Fast Track certification for the small solution.
Figure 13. 20-TB certification
20-TB certification
23 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
Figure 14 shows the Data Warehouse Fast Track certification for the mid-size solution.
Figure 14. 45-TB certification
45-TB certification
EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
24
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
Bill of materials
25 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
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-M3-C420
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 plug-IEC320/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-002-C420
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
26
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
27 EMC XtremIO for Microsoft SQL Server 2014 Data Warehouse Fast Track Reference Architecture
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
28
References
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 documentation
Microsoft documentation
top related