Dell PowerEdge R940 385TB Data Warehouse Fast Track Reference Architecture for - Page 6

Microsoft SQL Server 2017: Data warehousing with improved column-store technology 1 Microsoft SQL Server 2017: Data warehousing with improved column-store technology Microsoft SQL Server 2017 has made significant improvements in data warehousing technologies and performance, including column-store features as well as many other improvements. Column-store indices offer great advantages over traditional row stores for analytics and data warehousing queries. They are ideally suited for the star schemas, and tables with billions of rows which are commonly seen. Some of their advantages for analytics include the following: • Up to 10X compression in data size: Data warehouses are very large by nature and the compression offered by column-store index technologies offers both space and cost savings as well as significantly increased performance. These benefits are possible due to the dramatically reduced I/O requirements given by the compression and coupled by the ability to only scan the specific columns required by each query. This compression also reduces the amount of memory required to hold a given number of rows from the source data warehouse. • Additional indices: SQL Server 2017 adds the capability to add B-Tree indices to column-storebased tables, which enables efficient single-row lookup. In addition to these architectural features, Microsoft has further optimized the processing of queries in column-store indices in the following ways: • Operator pushdown: Pushdown refers to moving both filter and aggregation query operations closer to the data, so that many of the filters and calculations can be done in the scan operators, dramatically reducing the volume of data that needs to be handled further on in-query processing. • Batch-mode processing: SQL Server 2017 includes enhancements in batch-mode processing that handles many rows at a time rather than serially performing calculations on each individual row. These batch operations are further optimized by leveraging Single Instruction Multiple Data (SIMD) vector processing CPU instructions in the Intel® architectures. 1.1 Dell EMC Data Warehouse Fast Track reference architectures for SQL Server 2017 Dell EMC and Microsoft have refreshed the DWFT reference architecture offerings with the latest technology advancements in database, server, and storage technology. PowerEdge 14G servers with Intel® Xeon® processors, SC9000 arrays, and SQL Server 2017 are the latest additions to the list of reference architecture components. The Dell EMC DWFT reference architectures for SQL Server 2017 are engineered jointly by Dell EMC and Microsoft. The hardware and software optimizations are tested by Dell EMC and the performance results are crosschecked by Microsoft. This approach presents a fast time to value using integrated, balanced, and verified architectures. 6 385TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R940 and SC9000 | 4032-RA-SQL