Executive SummaryIn today’s world, organizations rely on analytics to gain business insight. The key to actionable insight is to get it in a timely manner. This enables organizations to act quickly and provide value. As big data becomes pervasive in the healthcare, retail, and construction and manufacturing industries (to name a few), it puts a strain on traditional analytics applications and infrastructure. This infrastructure now has to process a greater volume of diverse data types in the same amount of time or faster. Using traditional analytics can slow down the ability to provide key insights in real time.

Traditional analytics infrastructure starts out with online transaction processing (OLTP) systems that host transactional data as it comes in. This is then cleansed using extract, transform, and load (ETL) infrastructure as it moves into a data warehouse. Finally, queries are run against the data warehouse, either directly or using analysis services (like Microsoft SQL Server* 2016). This complex architecture is expensive, and managing the data flow requires all of the stages to work in harmony.

In this paper, we present SQL Server 2016, which solves the problem by allowing customers to create a columnstore index on transactional data so that customers can run an online analytical processing (OLAP) query on the OLTP database in real time. This process is known as operational analytics, or hybrid transactional/analytical processing (HTAP). Operational analytics drastically cuts down time to meaningful insights, as there is no data latency associated with ETL or with creating and consequently updating a data warehouse. There is also a significant cost savings as you reduce the number of systems needed to produce real-time insights and to upgrade to high-performance Intel® Xeon® processor-based configurations that can transform data in record time. In addition, licensing costs also decrease as fewer processing stages are needed. Microsoft SQL Server 2016 can do this for disk-based tables as well as in-memory optimized tables.

Intel and Microsoft have collaborated to enable and optimize operational analytics for SQL Server 2016 on the Intel® Xeon® processor E5-2699 v4 product family. In this paper, we present a performance study of operational analytics for disk-based tables, running business analytics queries on a database while it is simultaneously processing OLTP transactions. We compare these results to scenarios where simultaneous OLTP and OLAP are attempted using SQL Server 2014 on the previous-generation Intel® Xeon® processor E5-2699 v3 product family. This study drives home the point that new features in SQL Server 2016 with the Intel® Xeon® processor E5-2699 v4 product family make running a hybrid workload with both OLAP and OLTP together a reality for customers, allowing them access to business-critical information in real time.

Table of Contents

Executive Summary . . . . . . . . . . . . . 1

Technology . . . . . . . . . . . . . . . . . . . . . 2

Intel® Xeon® Processor- Based Systems . . . . . . . . . . . . . . . . 2

The Intel® SSD DC Family for PCIe* . . . . . . . . . . . . . . . . . . . . . . 2

Intel® RAID Controllers . . . . . . . . 2

Microsoft SQL Server* 2016: Data Warehousing with Improved Columnstore Technology . . . . . . 2

System Configuration . . . . . . . . . . . . 2

Workload . . . . . . . . . . . . . . . . . . . . . . . 3

Building the Database . . . . . . . . . 3

OLTP Workload . . . . . . . . . . . . . . . 4

Queries Used . . . . . . . . . . . . . . . . . . 4

Methodology . . . . . . . . . . . . . . . . . . 4

Results . . . . . . . . . . . . . . . . . . . . . . . . . 4

Conclusion . . . . . . . . . . . . . . . . . . . . . . 7

Real-time operational analytics with the Intel® Xeon® processor E5 family

AuthorsVaishali Paliwal

Intel Corporation – Software and Solutions Group

Hamesh PatelIntel Corporation –

Software and Solutions Group

Fast and Efficient Business Analytics

Fast and Efficient Business Analytics

white paper

The intended audience for this paper includes data warehouse customers, IT executives and managers, solution architects, data analytics experts, and infrastructure planners.

Technology Intel® Xeon® Processor-Based Systems

We used the Intel® Xeon® processor E5-2699 v4 product family for our study. We relied on throughput capability of these processors to deliver the performance required to run OLTP and OLAP on the same system simultaneously. Intel® Xeon® processors are designed for the most mission-critical workloads and the always-on enterprise. These processors combine large memory capacities with leading performance, reliability, and virtualization capabilities to keep the data center supplying business advantage without interruption.

The Intel® SSD DC Family for PCIe*

To ensure that data centers can keep up with the speed of business, we designed our I/O infrastructure using the extraordinary performance and reliability of Intel® Data Center SSDs. By pairing Intel® Data Center SSDs with Intel® CPUs, chipsets, firmware, and drivers, along with Intel® Cache Acceleration Software, we built a seamless system, enabling two different workloads to run simultaneously without interfering with each other. This provides a competitive edge to businesses with up-to-the-minute insights using analytics on live transactional databases.

Intel® RAID Controllers

The SAS-based Intel® RAID Controller RS25SB008 features the dual-core LSI* SAS 2208 RAID-on-chip (ROC) controller, which provides SAS 2.0 compliance with data-transfer rates of 6Gb/s, and offers significant performance enhancements for solutions with 6Gb/s SAS drives. The read/write performance ideally suits the controllers for a broad range of application workloads, such as enterprise data center, cloud computing, and content applications.

Microsoft SQL Server* 2016: Data Warehousing with Improved Columnstore Technology

Microsoft SQL Server* 2016 has made significant improvements in columnstore technologies and performance. Traditionally, row-store indices are used to ensure fast throughput of OLTP transactions, while columnstore indices offer a great advantage for complex data-warehousing queries. Columnstore indices in general provide up to 10x query performance gains over traditional row-oriented storage and 7x data compression over uncompressed data sizes.¹ Until now, a separate database needed to be created from a source row-based database to take advantage of columnstore indices for query processing. SQL Server 2016 enables filtered, non-clustered columnstore indices in addition to traditional row-based indices, thus providing the capability of running complex data warehouse queries using columnstore indices while row-store indices on the same table are used for transactional processing.

White Paper | Fast and Efficient Business Analytics

Figure 1: Operational analytics system setup

For our study, we used a server based on 2 Intel® Xeon® E5-2699 v4 processors. We used 16GB DDR4 memory, with 2 DIMMs per channel, giving us a total system memory of 384GB. Attached to the platform, we used 1 storage bay connected through a dual-port Intel® RAID Controller RS25NB008. We used this directly attached storage unit for storing database files and backup databases; tempdb was used for SQL Server 2016* as a temporary workspace for query processing and SQL Server 2016 transactional log files. We used Windows Server* 2012 R2 as the OS for the system. Figure 1 shows our setup for the workload using the Intel® Xeon® processor E5-2699 v4 with SQL Server 2016; we had a similar setup with the Intel® Xeon® processor E5-2699 v3 with SQL Server 2014 for comparison.

System Configuration

2

White Paper | Fast and Efficient Business Analytics

Workload The operational-analytics workload is comprised of simultaneous OLTP & analytics processing based on the TPC-C* and TPC-H* schemas to represent both OLTP and OLAP. The OLTP workload has 5 different OLTP transactions, including new order, payment, delivery, stock level, and order status. We used a workload driver software interface to run OLTP transactions with a set number of drivers and users. A subset of ad-hoc queries were derived from the TPC-H benchmark standard (see appendix):

• Query 1 represents revenue change forecast. • Query 2 represents profit measure. • Query 3 represents order priority and order count. • Query 4 represents customer distribution. • Query 5 represents item and supplier relationship. • Query 6 represents revenue.

OLTP and analytics queries were run on dedicated cores using SQL Server* 2016’s resource-pool configuration. We used the resource pool to allocate 75% of the cores for business-critical OLTP transactions with degree of parallelism (DOP) of 1 and 25% of the cores for analytical queries with a DOP equal to the number of cores. SQL Server 2016 uses a classifier function configuration to determine the credentials of the incoming query to allocate the correct resource pool.

Building the Database We built an OLTP database with 2,000 warehouses using SQL Server 2016. The database was derived from the TPC-C schema, consisting of 12 different tables. Tables consisted of row-based indices, which were either clustered or non-clustered. In addition, 4 tables—customer, order line, orders, and stock—also had non-clustered columnstore indices (NCCI) to speed up the complex data-warehouse queries. These NCCIs were maintained in real time to ensure data-warehousing queries were looking at the most recent data. To keep the OLTP throughput high, updates to columnstore indices were minimized by filtering to update indices only for cold data. Orders that were still open and not in a delivered state were not updated to columnstore indices.

For comparison with the SQL Server 2014 implementation, which did not support filtered NCCI, we used the same database schema and added non-clustered row-based indices on the same 4 tables (customer, order line, orders, and stock).

This schema, with a set of indices capable of servicing both OLTP transactions and OLAP queries, makes it possible to run OLTP and OLAP on the database at the same time.

In Table 1 below, you can find the specifics about the components we used in our configuration. All components in system 1 and system 2 are same except for the processors as noted below.

Platform

# Sockets 2

Chipset Intel® C610 series chipset

Networking Dual-port Chelsio* T520 adapter

Storage

SSDs – Data + tempdb + Backups Intel® SSD DC P3700 Series (800GB, 2 .5” SATA 3 .0) - x8

SSDs – Log Intel® SSD DC P3700 Series (800GB, 2 .5” SATA 3 .0) - x4

RAID Controller Intel® RAID Controller RS25NB008 (PCIe* x8 Gen3) - x1

Memory

Memory Type DDR4

DIMM Size & Type 16GB LRDIMM Dual Rank

Memory Frequency (MHz) 2133

DIMMs/Channel 2

Total Memory (GB) 384

Software

Database Software Microsoft SQL Server* 2016 Enterprise RTM

OS Distribution Microsoft Windows Server* 2012 R2

Processor Intel® Xeon® processor E5-2699 v4

# Cores/Threads 22/44

Base 2 .2GHz

Processor Intel® Xeon® processor E5-2699 v3

# Cores/Threads 18/36

Base 2 .3GHz

CPU SYSTEM 1

CPU SYSTEM 2

Table 1: Hardware and software configuration for systems used in this study

3

White Paper | Fast and Efficient Business Analytics

OLTP Workload We used a workload driver interface to generate OLTP transactions to the system under test. The OLTP workload consisted of 5 different transactions, including new order, payment, delivery, stock level, and order status. We maintained a steady state transaction mix such that all of the CPU cores dedicated to the OLTP workload (as configured in SQL Server 2016) were fully utilized.

Queries UsedFor the purpose of this study, we focused on 6 analytics queries to simulate real-world scenarios:

• Query 1 represents revenue change forecast. • Query 2 represents profit measure. • Query 3 represents order priority and order count. • Query 4 represents customer distribution. • Query 5 represents item and supplier relationship. • Query 6 represents revenue.

MethodologyThe methodology we followed consisted of first starting the OLTP workload from the driver interface and waiting for it to reach steady state for 20 minutes. Once the OLTP workload reached steady state, we launched 6 analytics queries sequentially over a period of time.

The performance tests we conducted show the following:

• Sampled OLTP throughput while OLAP queries were running.

• Elapsed query time for each of the 6 OLAP queries running alongside OLTP transactions.

Figure 2 highlights the two configuration metrics (above) as measured against the SQL Server 2016 implementation with filtered NCCI and with SQL Server 2014 implementation without NCCI. We compared OLTP throughput and elapsed OLAP query times in both cases.

Results We compared the performance of the operational analytics workload for OLTP transaction throughput and OLAP query times for the Microsoft SQL Server* 2016 and Intel® Xeon® processor E5-2699 v4 implementation and the Microsoft SQL Server 2014 and Intel® Xeon® processor E5-2699 v3 implementation as outlined above.

Details

OLTP performance on the Intel® Xeon® processor E5-2699 v4 with Microsoft SQL Server 2016 with filtered NCCI is up to 2x better than performance on the Intel® Xeon® processor E5-2699 v3 with Microsoft SQL Server 2014 without filtered NCCI. This improvement in performance is due to the increase in the number of cores (66 total cores for OLTP workload) in the Intel® Xeon® processor E5-2699 v4. It is also attributed to very efficient SQL Server 2016 columnstore index creation for analytics during the OLTP workload. In contrast, SQL Server 2014 has to create row-based indices for simultaneous analytics queries, which consumes more core cycles. As a result, the SQL Server 2016 implementation outperforms SQL Server 2014.

Figure 2: Study setup compares the Microsoft SQL Server* 2014 and Intel® Xeon® processor E5-2699 v3 implementation to the Microsoft SQL Server 2016 on an Intel® Xeon® processor E5-2699 v4.

4

White Paper | Fast and Efficient Business Analytics

A comparison of time elapsed between the 2 configurations shows that the Microsoft SQL Server 2016 and Intel® Xeon® processor E5-2699 v4 implementation with filtered NCCI shows an average of 4x reduction in query times, with individual queries showing improvement between 2x to 6x. Table 2 shows the elapsed query time for each individual query for both configurations.

Intel® Xeon® processor E5-2699 v3 + Microsoft SQL Server* 2014

Intel® Xeon® processor E5-2699 v4 + Microsoft SQL Server* 2016

Query # Query Type Elapsed Query Time (Seconds) Elapsed Query Time (Seconds) Improved by Factor

1 Revenue Change Forecast

4.87 1.05 4.64

2 Profit Measure 15.38 4.72 3.26

3 Order Priority and Order Count

34.06 10.57 3.22

4 Customer Distribution 5.49 1.54 3.56

5 Item and Supplier Relationship

2.08 0.72 2.89

6 Revenue 50.42 7.49 6.73

Figure 3: OLTP throughput while running simultaneously with OLAP – comparison of the Intel® Xeon® processor E5-2699 v3 with Microsoft SQL Server* 2014 to the Intel® Xeon® processor E5-2699 v4 with Microsoft SQL Server 2016.

Table 2: Elapsed query time for each of the 6 queries on the Intel® Xeon® processor E5-2699 v4 with Microsoft SQL Server* 2016 configuration and the Intel® Xeon® processor E5-2699 v3 with SQL Server 2014 configuration.

We found Intel® Xeon® processor E5-2699 v4 + Microsoft SQL Server* 2016 delivers faster query processing, resulting in a reduced elapsed query time due to hardware and software advances. The Intel Xeon® processor E5-2699 v4 has 22 threads for OLAP processing. In addition, SQL Server 2016 implementation of non-clustered column store indexes allows for faster batch processing of queries. SQL Server 2014 uses row store indexes for OLAP which is generally slower compared to using column store indexes for analytics queries.

5

White Paper | Fast and Efficient Business Analytics

As we continue to run the OLTP transactions, the non-clustered columnstore index is constantly being updated in real time to service analytics queries while maintaining OLTP throughput. In the Intel® Xeon® processor E5-2699 v3 with Microsoft SQL Server* 2014 without filtered NCCI implementation, the row-based index needs to be updated, which results in slowing down OLTP throughput, as well as slower analytics performance. The improvements that we have made with Microsoft SQL Server 2016 combined with the power of the next-gen Intel® Xeon® processor E5-2699 v4 make it possible for customers to benefit from simultaneous OLTP and OLAP in the real world.

Figure 4: OLAP elapsed query time comparisons for the Intel® Xeon® processor E5-2699 v3 with Microsoft SQL Server* 2014 configuration and the Intel® Xeon® processor E5-2699 v4 with SQL Server* 2016 configuration while processing OLTP transactions simultaneously.

Figure 5: Total elapsed query time for 6 queries, compared between the Intel® Xeon® processor E5-2699 v3 with Microsoft SQL Server* 2014 configuration and the Intel® Xeon® processor E5-2699 v4 with SQL Server* 2016 configuration while processing OLTP transactions.

6

Conclusion • Operational analytics implementation is becoming increasingly important for customers as they start to rely on up-to-date

big data analytics for business insights.

• Intel and Microsoft have partnered together to enable and optimize performance with filtered NCCI to provide a solution for businesses to benefit from current data analytics while allowing simultaneous transactional processing on the same server.

• Our analysis compares the performance gain for OLTP and Analytics query times with key complex queries using Microsoft SQL Server 2016 and Intel® Xeon® processor E5-2699 v4 implementation with filtered NCCI versus the Intel® Xeon® processor E5-2699 v3 with Microsoft SQL Server* 2014 implementation without filtered NCCI. We see up to 2x improvement on OLTP throughput calculated and 4x faster OLTP query times.

Endnotes For more about Columnstore indexes <https://technet.microsoft.com/en-us/library/gg492088(v=sql.120).aspx> Get started with Columnstore for real time operational analytics. <https://msdn.microsoft.com/en-us/library/dn817827.aspx>“Real-Time Operational Analytics with SQL Server 2016,” Channel 9, March 2016. <https://channel9.msdn.com/Events/DataDriven/SQLServer2016/Real-Time-Operational-analytics>“Hybrid Transaction/Analytical Processing,” Gartner, January 2014. <https://www.gartner.com/doc/2657815/hybrid-transactionanalytical-processing-foster-opportunities>“What is HTAP?” Business Analytics & Digital Business, December 2014. <http://timoelliott.com/blog/2014/12/what-is-htap.html>“SQL Server Blog,” Microsoft Information Platform, June 2016. <https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/01/sql-server-2016-is-generally-available-today/>1. Source: https://technet.microsoft.com/en-us/library/gg492088(v=sql.120).aspx

White Paper | Fast and Efficient Business Analytics

AcknowledgementsSadashivan Krishnamurthy, Microsoft CorporationJamie Reding, Microsoft CorporationSree Kolla, Microsoft CorporationLindsey Allen (ZHU), Microsoft Corporation

Copyright © 2016 Intel Corporation. All rights reserved. Intel, the Intel logo, Intel Inside, the Intel Inside logo, and Xeon are trademarks of Intel Corporation or its subsidiaries in the U.S. and/or other countries.*Other names and brands may be claimed as the property of othersSoftware and workloads used in performance tests may have been optimized for performance only on Intel® microprocessors. Performance tests, such as SYSmark* and MobileMark*, are measured using specific computer systems, components, software, operations, and functions. Any change to any of those factors may cause the results to vary. You should consult other information and performance tests to assist you in fully evaluating your contemplated purchases, including the performance of that product when combined with other products. For more information, visit intel.com/performance.The workload used is derived from portions of TPC Benchmark™ C and TPC Benchmark™ H, as such, it is not comparable to published TPC-C and TPC-H Benchmark results. All performance data contained in this report was obtained in a rigorously controlled environment. Results obtained in other operating environments may vary significantly.1216/RF/QUA/PDF Please Recycle 335368-001US