Category: SQL Server

by Miguel Ángel Motos 2019-02-13

Speed Up SQL Server with ColumnStore Index

In this serie of articles, we are going to talk about indexes oriented in columns for SQL Server (Column Store Index), this is an introductory post in which we will include the different aspects that we will deal with later in each of them.

The Column Store indexes appeared in SQL Server 2012, but it was on 2016 when they began to develop their full potential. These indexes can be up to 10 times faster than traditional indexes oriented to rows (RowStore Index) and, if we focus on the size that they occupy, data compression can be up to 7 times higher than uncompressed data.

1.- Index ColumnStore in SQL Server 2012

The firsts lights of Index ColumnStore where:

• A table could have only one non-clustered ColumnStore index.
• The ColumnStore Index was read-only. That was an impediment because if we wanted to perform an INSERT, UPDATE or DELETE operation, we had to delete the ColumnStore index, carry out the relevant operations and then create it again.
• ColumnStore indexes began to use segment compression.

2.- Features added in SQL Server 2014

Later, we come to the SQL Server 2014 version, where the changes on the ColumnStore indexes were the following:

• The possibility of creating a Clustered Column Store index is added per table, with the restriction that the table can not contain more indexes.
• You can update, insert or delete data in tables that contain the Clustered Column Store Index.
• The compression option “Archive” is added, which can compress harder the ColumnStore indexes for tables with a low number of DML operations (INSERT, UPDATE, DELETE).

3.- Index ColumnStore SQL Server 2016, the great change

Is in SQL Server 2016 version where they became really usefully:

• A table can contain a non-clustered ColumnStore index that can be updated.
• In-memory tables can have a Coulmn Store index. You can create it when the table is generated or add it later with the ALTER TABLE command. In previous versions, only disk-based tables could have a Column Store index.
• A clustered ColumnStore index can have one or more Non-clustered RowStore Indexes.
• Compatibility with the main keys and foreign keys in ColumnStore indexes.
• ColumnStore indexes have an option to delay compression, so if any row have to be modified it does not have too much impact on server performance.

4.- Last update in ColumnStore indexes for SQL Server 2017

• Calculated columns from SQL Server 2017, a clustered  ColumnStore index can contain a calculated non-persistent column.

From our experience as consultants, we have analyzed a lot of SQL servers from the 2012 to the 2017 versions and we have not seen these types of indexes barely applied. It can be considered that they are not well known and their capacity to improve the performance of queries is ignored.

5.- ColumnStore vs RowStore Indexes

The question we all expected, the first thing that comes to mind is:

When will we use Column Store Index and when will we use Index RowStore Index?

Thinking about this question, we see that both indexes can coexist in the same environments. The difference is that Column Store indexes are more suitable in OLAP (On-Line Analytical Processing) tables and RowStore indexes are more suitable for OLTP (On-Line Transactional Processing) tables. We use Column Store indexes into fact tables and tables of large amount of data like Datawarehouse. We can use non-clustered Column Store indexes to make Real Time Operational Analytics in OLTP environment.

A real problem is that we can find a lot of blocks when we do DML operations (INSERT, UPDATE or DELETE) on tables with Clustered Colum Store Indexes. So we should use this indexes on OLAP environments and not on OLTP.

if you are interested in SQL Server don’t miss this posts:

Get Live Index creation % in SQL Server

Breaking Up the ColumnStore Indexes

Contact Us to know more.

Contact Us