Reference Architecture for SQL Server 2016 In-Memory Columnstore

Solution Overview

The Microsoft in-memory columnstore technology provides hosting service providers the opportunity to support following key business scenarios:

  • Provide affordable high performance data warehouse solutions to consumers without any additional investment in a dedicated data warehouse system.

This powerful technology improves query performance and data compression by up to 10x in turn reducing the data warehouse storage cost significantly. This document describes the following:

  • Managing in-memory columnstore index and their reference architecture implementation guidance
  • Getting better performance with in-memory columnstore index.

These scenarios are the focus of this document. Each scenario's architecture is described from an architectural point of view.

Intended Audience

The audience for concepts explained in this document are the Infrastructure Consultants, Architects and Database Administrators of hosting service providers. Hosting service providers who are considering utilizing their in-house hardware and software investments towards the opportunity to increase bottom line revenue. Along with in-house hardware Hosting service providers can also utilize the Microsoft Hybrid Cloud Platform in incremental stages to offer ancillary services around in-memory columnstore to benefit their consumers.

Architectural Overview

The Reference Architecture gives an overview of how in-memory technology fits in the overall solution architecture of a service provider. At a high level we can visualize this as three layered architecture which includes Infrastructure, Platform and Application. Each layer has its own importance and association.

Infrastructure

The components within infrastructure layer represents the technical capabilities of service provider which are required to host on-premise platform, software and applications (if any). The infrastructure layer of a service provider facilitates IaaS services to consumers to host in-memory columnstore enabled databases on physical hardware as well as on virtualized environment, in either scenario the compute power - physical memory and compute cores required to manage in-memory workload are dedicated and can be scaled up or scaled down on the need basis. At the same time in a given database not all tables are in-memory columnstore workload eligible. The network component of this layer represent the physical network switches, routers, firewalls etc. and is used to establish two-way communication between infrastructure, software, platform and application.

Platform

Platform layer works in conjunction with infrastructure and application to provide database service. This service offers hosting columnstore indexes on tables which are in-memory as well as disk based. Consumers' databases get hosted on this platform and in-memory columnstore indexes can be enabled based on consumers' requirements. In-Memory columnstore indexes can be created on the in-memory tables. Enabling and configuring in-memory columnstore index in a given table is straight forward. Two key things we have to be keep in consideration are availability of compute resources and whether a table is eligible for creating in-memory columnstore index or not.

Application

Application layer works in conjunction with infrastructure and platform and provide data to end users for their analysis. There could be a variety of applications which might want to gain access to data from an in-memory OLTP database and leverage the performance of in-memory columnstore indexes.

Desktop

Accessing database tables with in-memory columnstore index from a desktop application is possible and there are no special changes or considerations one has to make. It is seamless to access database tables with in-memory columnstore index from a desktop because under the cover everything this is taken care at the database platform side and from an application perspective it is as good as connecting to any standard SQL database. This means be it a .NET thick client or a Java or any other application as long as they can connect to SQL databases they would be able to leverage database table with in-memory columnstore index.

Mobile

Accessing database tables with in-memory columnstore index from mobile devices such as phones and tablets is seamless and transparent. Mobile apps can connect to SQL databases and leverage in-memory columnstore index enabled tables. This holds good for different mobile platforms like iOS, Android and Windows.

A Mobile device such as phone and tablet can connect to database tables with in-memory columnstore index using APIs or a direct mode in either cases from a development point of view there is no change as the in-memory columnstore technology works at server side. However high performance of the system becomes instantly visible based on query response time.

Web

Accessing databases with in-memory columnstore index from Web apps is easy and seamless just like accessing any other database in SQL Server. Web application developed on variety of platform like .NET as well as third party platform like Java, Python, PHP etc., can leverage database tables with in-memory columnstore index seamlessly because in-memory columnstore technology works at server side, this means web developers from any platform need not to worry about any changes into their SQL Server database access method. Same hold true for web based reporting applications, these can connect to database table with in-memory columnstore index without any changes and can leverage high performance response back from the database.

In-Memory Columnstore Components

At technology level, in an in-memory columnstore index each index column in each row group is stored separately, in a structure called a segment. Each segment is stored as a Large Object (LOB) value, and stored in LOB allocation unit for the partition. Segments are the basic unit of manipulation and can be read and written separately. This figure illustrates the encoding and conversion of a set of values for multiple index columns into several segments.

The table in the figure has been divided into three row groups where all four columns from the table are defined as part of the columnstore index. We end up with 12 compressed column segments, three segments for each of the four columns.

If a SQL Server 2016 memory-optimized table has a clustered columnstore index, new rows will be allocated from a specific memory allocator. This allows SQL Server to quickly identify the rows that are not yet compressed into segments in the columnstore index.

Architecture Design & Primary Scenarios

With the growth in hardware technology, primary memory size of server systems has grown at a rapid and at the same time it became affordable, organizations in various business scenarios are using in-memory databases to accelerate performance of their transactional systems by up to 30 to 40 times. In-Memory columnstore indexes can achieve better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes.

With the in-memory columnstore Index the hosters can provide the high performance for the in-memory OLTP table without actually opting to enhance the existing infrastructure. I.e. with this new feature the OLTP tables can provide query performance of a data warehouse table and hence can be used for the data warehousing and reporting purposes.

Hosting Service Providers

Hosting service providers can leverage the innovative solutions discussed in this document by speeding up OLTP workload with memory-optimized columnstore indexes up to 30x faster compared to traditional disk based tables and thereby delivering faster business insight with faster querying and reporting. With a combination of hybrid architecture, a service provider can not only offer to run these mission critical workloads in their environment but they can also offer enterprise grade security and disaster recovery. The innovation is really Microsoft constructing, proving, and documenting how the database tables with in-memory columnstore index with a hybrid architecture meets and exceed hosting service provider needs.

High Performance Data Warehouse Scenario

Scenarios for using In-Memory Columnstore Index

In-Memory columnstore index can be used to offer high performance real time data warehouse and operations workload to run in-memory with zero or minimal latency. This gives flexibility to the service providers to enable consumers to utilize existing in-memory OLTP workloads to get further extended as a data warehouse or operational solution without making any additional investment for new data warehouse or operational systems.

Query Performance Design Considerations

Query performance is always one of the key considerations in any data systems where read and write operations are involved. Indexes play a very crucial role in boosting query performance and if used in a right way they make data systems more productive. In-Memory columnstore indexes can achieve up to 10x better performance on analytics and data warehousing workloads and up to 10x better data compression over the uncompressed data size. These recommendations here will help queries achieve the optimal query performance that columnstore indexes are designed to provide.

Design Considerations

Here are few key recommendations for achieving the high performance from a columnstore indexes:

  1. Organize data to eliminate more Rowgroups from a full table scan

    In common case in traditional data warehouse, the data is indeed inserted in time order and analytics is done in time dimension. For example, analyzing sales by quarter. For this kind of workload, the rowgroup elimination happens automatically. In SQL Server 2016, you can find out number rowgroups skipped as part of query processing.

  2. Plan for enough memory to create Columnstore indexes in parallel

    Creating a columnstore index is by default a parallel operation unless memory is constrained. Creating the index in parallel requires more memory than creating the index serially. When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.

    Beginning with SQL Server 2016, the query will always operate in batch mode. In previous releases, batch execution is only used when degree of parallelism is greater than one.

In-Memory Columnstore Performance

In-Memory columnstore indexes achieve high query performance by combining high-speed in-memory batch mode processing with techniques that greatly reduce IO requirements. Since analytics queries scan large numbers of rows, they are typically IO-bound, and therefore reducing IO during query execution is critical to the design of columnstore indexes. Once data has been read into memory, it is critical to reduce the number of in-memory operations.

Columnstore indexes reduce IO and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.

Performance Enhancements

SQL Server 2016 Release Candidate (RC1) introduces these features for columnstore performance enhancements: -

  1. AlwaysOn supports querying a columnstore index on a readable secondary replica.
  2. Multiple Active Result Sets (MARS) supports columnstore indexes.
  3. Single-threaded queries on columnstore indexes can run in batch mode. Previously, only multi-threaded queries could run in batch mode.
  4. The SORT operator runs in batch mode.
  5. Multiple DISTINCT operation runs in batch mode.

Note: Beginning with SQL Server 2016 Release Candidate (RC1), you can define one non-clustered index on a clustered columnstore index.

Backup and Restore

Memory optimized tables having in-memory columnstore index are backed up as part of regular database backups so there is nothing special to manage backups of databases with memory optimized tables. However backup of memory optimized tables will be much larger than compared to actual memory footprint. A full backup of a database with one or more memory-optimized tables consists of the allocated storage for disk-based tables (if any), the active transaction log, and the data and delta file pairs (also known as checkpoint file pairs) for memory-optimized tables. We can also do differential backups of databases with memory optimized tables.

In the similar way the restore of databases with memory optimized tables is similar to disk based tables, only addition to this is that memory optimized tables must be loaded into memory before database is available for user access.

Summary

The performance benefits available by using in-memory columnstore indexes is caused by two major factors. One is the I/O savings caused by the new index structure, the other is batch mode execution. With SQL Server 2012, batch mode execution was unfortunately limited to only a handful of operators, causing many common query constructs to force a query back into row mode execution, forfeiting the performance benefit of batch mode. Most of these limitations have been lifted in SQL Server 2016.

In-memory columnstore indexes give high performance gains for analytic queries that scan large amounts of data, especially on large tables. Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, since they tend to require full table scans rather than table seeks.