Overview of Columnstore Indexes in SQL Server

As Aaron Bertrand has written about recently, SQL Server 2016 SP1 opens up a lot of new possibilities for Standard, Web, and Express editions of SQL Server, including new options for in-memory technologies such as columnstore indexes and memory-optimized tables (albeit with performance limits). The capabilities mentioned in this post, which have evolved significantly in recent versions, are applicable to SQL Server 2016 SP1.

Comparison of In-Memory Options Available in SQL Server

Before we get into columnstore indexes as the main focus of this post, let’s first take a brief look at the types of in-memory features available in SQL Server:

• Columnstore Indexes
• Memory-Optimized Tables

It can be quite confusing to discern the differences between the in-memory technologies in SQL Server. Each feature can be referred to by various names, and those names can be easily misunderstood (for instance, the difference between clustered and nonclustered columnstore has nothing to do with ordering of the data). Sometimes the features are used simultaneously, so getting clarity on each component can be a challenge.

Each of these in-memory features target fundamentally different types of workloads. Following is a brief summary of each feature:

For brevity, the other in-memory data storage technologies available in the Microsoft platform are out of scope for this post:

• SQL Server Analysis Services
• Azure Analysis Services
• Power BI Desktop, Power BI Service, and Power BI Embedded
• Power Pivot for Excel, and Power Pivot for SharePoint
• Spark on HDInsight

Many of the in-memory features in the Microsoft platform rely on the xVelocity (formerly known as VertiPaq) engine. The implementations do differ somewhat between products, such as the requirements for data to be truly memory-resident.

The remainder of this post will focus on the two columnstore technologies in SQL Server: clustered and nonclustered.

Primer on Rowstore vs. Columnstore

Traditionally, data in SQL Server has been stored in a rowstore format (not to be confused with the term rowgroup which is a different concept). Rowstore format is referred to as a b-tree if there’s an index on the table, or a heap if the table is unindexed.

Following is a simplified, conceptual example of a table in rowstore format in which the entire row of data, for all columns, is stored on a single page in SQL Server:

Conversely, columnstore format (conceptually) stores the distinct values of each column on separate pages in SQL Server:

As a general rule of thumb, rowstore format is more suitable for OLTP workloads in which DML operations on individual transactions are the focus. Conversely, the columnstore format tends to suit analytical workloads which produce aggregate query results by scanning a large number of rows and only retrieve a small number of columns from the table. This rule of thumb, however, can get murky for mixed workload situations.

Advantages of Columnstore Technologies

Integrated in SQL Server. Because the in-memory technologies are integrated in the SQL Server engine, a separate platform is not required for a specialized implementation. This makes running mixed workloads more cost-effective. We can use familiar tools and skills for managing the columnstore objects, and there’s usually no application changes or database redesign required. We can query SQL Server catalog views for managing and tuning the columnstore objects.

Reduced storage. As shown in the above images, columnstore is able to take advantage of repeated data values in a column and not store those repeated column values redundantly. Reducing the storage of redundant values results in a high level of data compression using xVelocity algorithms, which translates into less storage requirements, reduced disk I/O, more cache hits, and better page life expectancy. Columnstore compression algorithms are most effective for low cardinality columns (ex: Disk name from the above example, which has a low number of distinct values), as opposed to high cardinality columns (ex: Measurement column from the above example, where the number of unique values is high).

Memory usage results in less disk activity. Returning results from memory is faster than returning results from disk. Reducing disk I/O, which is a common bottleneck, leads to improved IOPS and throughput.

Optimized query performance for analytical queries. Columnstore indexes are able to improve analytical query performance with techniques such as:

• Column elimination. Because data is physically stored in a columnar structure, only columns which are referenced in a query need to be accessed. This ability to skip columns not referenced in the query is significantly different from a traditional rowstore table which always has to retrieve a full row, even if only one or two columns are requested in a query. Column elimination makes columnstore indexes particularly suited to analytical queries which frequently only retrieve a few columns from a table.
• Segment elimination. Columnstore computes and stores the min/max values for each segment associated with a column. This allows entire segments to be skipped which do not match the query predicate (i.e., the where statement). Segment elimination is conceptually similar to how partition elimination works. Starting in SQL Server 2016, we can have a standard nonclustered (b-tree) index alongside a clustered columnstore index which also can influence segment elimination. If techniques are employed to purposely load data in a sorted order, segment elimination can be even more effective because the min/max value ranges are smaller with less overlap.
• Batch mode. Certain query operators perform in a batch mode, typically in batches of 900 rows, which improves query efficiency. T-SQL queries need to be constructed purposely in order to get maximum benefit from columnstore in batch mode, versus inadvertently reverting to row mode (wherein aggregations are performed one row at a time). Supported batch mode operators in each version of SQL Server are listed on MSDN.
• Pushdown of aggregates and predicates. The query optimizer pushes down aggregations and predicates to the lowest possible level. This objective of this is to minimize the number of rows which are passed through query operations.

Disadvantages of Columnstore Technologies

As the columnstore features have evolved since originally introduced in SQL Server 2012, the disadvantages are reducing. The tradeoffs for achieving the benefits listed in the previous section include:

Server memory requirements. Server memory requirements are based on data volumes, effectiveness of compression (i.e., data cardinality, use of minimal data types, etc.), and if columnstore is being used in conjunction with memory-optimized tables. A columnstore index is not required to be fully memory-resident on a disk-based table, but it is with a memory-optimized table.

Overhead of handling updates and deletes. Flexibility with data loading options have significantly improved since the initial releases, and techniques (such as the deltastore) exist for optimizing data loads. However, it still holds true that columnstore is most suitable for data which is not frequently updated (‘cold’ data rather than ‘hot’ data from an update perspective).

Limitations on datatypes, T-SQL support, and features. There are significantly fewer columnstore restrictions as of SQL Server 2016 (for example, in early releases a columnstore index was not updateable). There are still some limitations on data types allowed and supported features (such as no computed columns).

Somewhat additional complexity. Inherently, columnstore technologies introduce more complexity to a solution (though certainly less complex than an entirely separate data storage solution). Data load processes may need to be adjusted to take full advantage of columnstore behavior (ex: optimal batch size and ordering of data). Queries may need to be tuned to achieve optimal performance (ex: batch mode).

Terminology Reference

We will conclude this post with a quick reference of terminology related to columnstore technologies in SQL Server. In alphabetical order:

Batch mode: Vector-based execution for query processing of multiple rows in batches of 900. Batch execution performs significantly better than row mode execution.

Clustered columnstore index (CCI): Represents the physical storage for an entire table structured as columnstore rather than rowstore format. The column-oriented format lends itself to substantial compression, which in turn reduces storage requirements and improves query performance due to reduced I/O. Like a traditional clustered index, a CCI is the actual master copy of the data (which is why the word ‘clustered’ is part of its name). However, the term CCI is actually a misnomer because there is no inherent sorting of the data (very unlike a traditional clustered index). CCIs are recommended for data warehousing workloads, particularly for tables with over 1 million rows.

Columnstore: Data which is physically stored in columnar format, but still presented to the user in rows and columns. A data page stores data from a single column, compressed into unique values, which is fundamentally different from the traditional rowstore storage.

Delete buffer: Holds the rows which have been logically deleted from a columnstore. Contents from the delete buffer will be omitted from query results (although the rows still exist in the columnstore).

Disk-based table: The traditional table structure in SQL Server where rows of data are stored in pages.

Deltastore: Applicable to columnstore, but not in-memory optimized tables. If a batch of data being loaded is < 102,400 rows or is rolling over into a new rowgroup because the previous rowgroup is full, the rows will be stored in the deltastore. When the threshold is met, a background process will compress the data and move it into the columnstore index. Conversely, if a large data load exceeds 1,048,576 rows, they will bypass the deltastore for loading directly to the columnstore. The deltastore is not column-oriented; it is a standard b-tree structure. Usage of the deltastore improves performance and reduces fragmentation of columnstore index. Query results will union the results from both the columnstore and the deltastore (minus the delete buffer).

Hybrid transactional analytical processing (HTAP): A platform which supports two types of workloads: transactional processing, as well as analytical/reporting type of queries. The objective is to perform real-time “operational BI” on the source data, while avoiding data movement and data duplication in a secondary data storage solution. HTAP is a good fit when the analysis can be conducted on a single system, without the need for integration of multiple systems.

Memory-optimized table: A row-based technology in which data is stored as individual rows without the traditional page structure. The lack of pages translates to no locking or blocking, but still has full ACID support. The table resides in memory and is accessed from natively compiled stored procedures for efficient data access. For tables specified as durable, a second copy of the table data is maintained on disk via checkpoint files. There are numerous differences in index handling and T-SQL support for memory-optimized tables vs. a traditional disk-based table.

Nonclustered columnstore index (NCCI): A columnar-oriented secondary index on top of a rowstore table for the purpose of analytical queries run directly on a transactional system (HTAP). The query optimizer can choose the rowstore table for satisfying OLTP queries (such as an individual seek), or the NCCI for serving analytical queries (such as a range scan). Because they are built on top of the rowstore table using selective columns needed to support analytical queries, NCCIs do not reduce the overall storage requirements (the way CCIs do); the entire purpose for a NCCI is around improving analytical query performance within an OLTP system.

Row mode: Query processing of each row one at a time. Row mode performs slower than batch mode.

Rowgroup: Groups of rows associated with a columnstore index which have been compressed into columnar form. Minimum size of a rowgroup is 102,400 rows. Maximum size of a rowgroup is 1,048,576 rows.

Rowstore: Data which are stored in the traditional row-oriented format on a data page. A rowstore could be a heap of unordered data, a b-tree index, or even a memory-optimized table.

Segment: A column of data within the rowgroup which is compressed and stored together. Closely related to the concept of a rowgroup, a segment can hold up to 1,048,576 distinct values.

Tuple-mover: A background process which manages the delta store and rowgroups. It will compress the data from the deltastore, move it into the columnstore index, and close the rowgroup when it has reached the maximum size.

Melissa Coates

Melissa Coates is a Business Intelligence Architect with SentryOne. Based in Charlotte, North Carolina, she specializes in delivering Analytics, Data Warehousing, and Business Intelligence solutions using on-premises, cloud, and hybrid technologies. Formerly a CPA, Melissa is ridiculously proud to be an IT geek and downright giddy to be a Microsoft Data Platform MVP. When Melissa steps away from the keyboard, you can probably find her hanging out with her border collie, paddle boarding, or playing in the garden. Melissa also blogs at sqlchick.com.