White Papers

Executive Summary

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 sizingtechniques used in SQL Server DWFT will properly size servers, based on I/O and CPU consumption. This consumption-based approach ensures your data warehouse can fully take advantage of your hardware investment.

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.

45 percent lower total cost of ownership with scale-up consolidation efficiencies, and options for fewer cores and high-frequency processors to drive down software licensing costs

Faster, lower-cost infrastructure management and a single integrated view of your IT infrastructure with HP OneView

3X faster administrative updates with 93 percent less downtime and 69 percent less operator time using industry- leading maintenance tools with HP Smart Update Manager

New Data Warehouse Features in SQL Server 2014

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 eliminates the need to build summary tables, thus further reducing ETL run times.

CCI is optimized for query performance. Our solution deliver 7x better query performance when using CCI. CCI accomplishes this by using a columnar format to compress the data by 10x or more, processing a set of rows in batches, and reading only the columns that are referenced in the query.

CCI is updateable allowing concurrent insert – both bulk import and trickle insert – of new data while query workload is running. This reduces the data latency from the time data is born to when it is available for querying.

About the DWFT Reference Architecture

The SQL Server DWFT reference architecture provides a scalable framework centered on balancing I/O to achieve maximum performance from SMP-based servers. SQL Server DWFT eliminates the complexity of sizing servers with data warehouses by providing a set of data consumption rates that properly balances performance between the disk subsystem, CPU, and memory.

This architecture is based on the HP ProLiant DL580 Gen8 and HP PCIe LE Workload Accelerator storage controller. This configurationis optimized for data warehouse (scan I/O) workloads and is rated by Microsoft for up to 90TB of compressed data.

More information on SQL Server DWFT reference architecture can be found here:

Enabling the power override setting on the HP PCIe LE Workload Accelerator product line is required to achieve the performance results below. A server reboot is required for the setting to be active and persist.

One 20GB tempdb file was stored on each ioMemory volume. In total, six tempdb data files were spread across six ioMemory volumes. The tempdb transaction log file was stored on the volume designated from log and staging files.

SQL Server Settings

Resource Governor
The Memory Grant % value was set to 12% of the memory allocated for row store runs and 25% for column store runs. The settings were changed in the Default resource pool.

Max Degree of Parallelism (MDOP)
MDOP was set to 30 for row store and 120 for column store. This provided the best scan rates for the respective runs.

The SQL Server service account was assigned the Lock Pages in Memory policy.

Trace Flags
Trace flag -T1117 was used to increase performance. This flag forces all data files in a file group to grow at once, which reduces “hot spots” of data pages. This ensures that all databases with more than one data file will grow properly across all the data mounts, which in turn ensures maximum physical I/O performance. Trace flag “–E” was omitted, as testing revealed a sharp increase in queue depth and latency which decreases scan performance. Therefore trace flag “-E” is not recommended with our flash technology.

The reference configuration is a 2 socket system rated for 25TB using the FTDW V4 methodology

1 Assumes a data compression ratio of 5:1
2 Percent ratio of the throughput to the row store throughput of the reference configuration.
3 Percent ratio of the throughput to the column store throughput of the reference configuration.
* Reported metrics are based on the qualification configuration, which specifies database size and SQL Server memory.

Summary

This solution went through hundreds of hours of testing and engineering to provide the most optimal and reliable configuration for the HP and SanDisk® SQL Server Data Warehouse Fast Track Reference Architecture. The HP PCIe LE Workload Accelerator simplifies storage configuration by reducing the importance of sequential I/O, as evangelized in previous Data Warehouse Fast Track Reference Architectures.

With 9000+ MB/s of database read throughput and a rated user data capacity 90TB, the HP ProLiant DL580 Gen8 with the HP PCIeLE WorkloadAcceleratordeliversindustry-leading, breathtaking performance with the ability to host extremely large data warehouses or a consolidation of data warehouses.