ColumnStore Indexes are great! They are a real performance booster for Data Warehousing and Reporting workloads. In combination with Clustered ColumnStore Indexes you get a huge compression benefit over regular RowStore Indexes (Clustered Indexes, Non-Clustered Indexes).

In other words, it’s a technology for storing, retrieving and managing data by using a columnar data format, called a ColumnStore.

Creating a Clustered ColumnStore Index is quite easy:

CREATE CLUSTERED COLUMNSTORE INDEX ccsi ON TableName
GO

 But is that all that you have to know about Clustered ColumnStore Indexes?
Not really, let’s explore this blog to understand more of this.

In discussions about columnstore indexes, we use the terms rowstore and columnstore to emphasize the format for the data storage. Columnstore indexes use both types of storage.



A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

A columnstore index physically stores most of the data in columnstore format. In columnstore format, the data is compressed and uncompressed as columns. There is no need to uncompressed other values in each row that are not requested by the query.This makes it fast to scan an entire column of a large table.

A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data such as a heap or clustered "btree" index.

A columnstore index also physically stores some rows in a rowstore format called a deltastore. The deltastore, also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. Each delta rowgroup is implemented as a clustered btree index.

A deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. The deltastore is a rowstore.

There are so many advantages with Clustered ColumnStore Indexes, which lead to massive performance improvements:

• Better Compression
• Batch Mode Execution
• Less IO & better Memory Management
• Segment Elimination

Creating a Clustered ColumnStore Index in SQL Server is quite easy as you see from the following example:

CREATE CLUSTERED COLUMNSTORE INDEX idx_ci ON FactFinance
GO

 You only need to specify the table name, nothing more. You even don’t need to worry about Clustered Key Columns, because this concept doesn’t apply to ColumnStore Indexes. Easy, isn’t it?

Let’s run a simple query with that Clustered ColumnStore Index in place:

-- Segment Elimination doesn't work quite well, because
-- we have a lot of overlapping Segments.

SELECT
DateKey,
SUM(Amount)
FROM FactFinance
WHERE
DateKey >= '20101229'
AND DateKey <= '20131228'
GROUP BY
DateKey
GO

 The query is quite fast, because SQL Server can use the Clustered ColumnStore Index for query execution. And the output from STATISTICS IO also shows you that not many LOB Logical Reads were needed for accessing the Clustered ColumnStore Index:



But what about these Segment Read and Segment Skipped metrics?

As you might know a ColumnStore Index is internally subdivided into so-called ColumnStore Segments. A ColumnStore Segment is always specific to a specific column and a Row Group. A Row Group contains about 1 million of rows. The following picture illustrates this very important concept:


Figure 1: Illustrating how a column store index is created and stored. The set of rows is divided into row groups that are converted to column segments and dictionaries that are then stored using SQL Server blob storage.

Source: http://research.microsoft.com/apps/pubs/default.aspx?id=193599

What is ColumnStore Segment Elimination?


The most important thing here is that SQL Server stores a Minimum and Maximum Value internally for each ColumnStore Segment. And based on these values SQL Server can perform so-called Segment Elimination. Segment Elimination means that SQL Server only reads those Segments (while accessing a ColumnStore Index) that contain requested data. You can think about it in the same way as Partition Elimination when you work with Partitioned Tables. But the elimination happens here at the ColumnStore Segment Level.

As you have seen in the previous picture, SQL Server wasn’t able to eliminate any segments during the ColumnStore Index access, because by default you have no sorting order in the ColumnStore Index. The sorting order of your data depends on how SQL Server reads the data in the Execution Plan when you create the ColumnStore Index:

As you can see the Clustered ColumnStore Index was created by reading from the Heap Table that initially contained the data. And therefore, you have no sorting order in the Clustered ColumnStore Index, and therefore the Segment Elimination can’t work perfectly for you.

How can you improve that situation?



Enforce a sorting order in your data by first creating a traditional RowStore Clustered Index, and change it to a Clustered ColumnStore Index! Ouch that hurt.

-- Now we create a traditional RowStore Clustered Index to sort our table data by
-- the column "DateKey".

CREATE CLUSTERED INDEX idx_ci ON FactFinance(DateKey)
GO

-- "Swap" the Clustered Index through a Clustered ColumnStore Index

CREATE CLUSTERED COLUMNSTORE INDEX idx_ci ON FactFinance
WITH (DROP_EXISTING = ON)
GO
You should have now sorted data in the Clustered ColumnStore Index, and Segment Elimination should work quite well:

-- Segment Elimination works better than previously, but still not perfectly.
SELECT
DateKey,
SUM(Amount)
FROM FactFinance
WHERE
DateKey >= '20101229'
AND DateKey <= '20131228'
GROUP BY
DateKey
GO
But when you look again at the output of STATISTICS IO, SQL Server still has to read a lot of segments, and only skips a few of them:



But why can’t SQL Server skip all segments other than the one we are interested in? The problem lies in the creation of the Clustered ColumnStore Index. Execution Plan, the ColumnStore Index Insert (Clustered) operator was running in parallel – across multiple worker threads. And these worker threads are again destroying the order of your data in the Clustered ColumnStore Index! You read your data pre-sorted from the Clustered RowStore Index, and then the parallel creation of the Clustered ColumnStore Index reshuffles your data… That hurts – again!

You can only solve that problem by creating the Clustered ColumnStore Index with a MAXDOP of 1:

CREATE CLUSTERED COLUMNSTORE INDEX idx_ci ON FactFinance
WITH (DROP_EXISTING = ON, MAXDOP = 1)
GO
This sounds terrible, and it is terrible!!! But it’s the only solution to let you preserve the ordering of your data in the ColumnStore Index. When you later read from the Clustered ColumnStore Index, you will then see that SQL Server was finally able to skip all Segments other than the one you are interested in:



Summary


ClusteredColumnStore Indexes are great. But by default, the Segment Elimination can’t be performed very well, because you have no predefined sorting order in your Clustered ColumnStore. Therefore, you should always make sure that the Segment Elimination works well, when you tune your ColumnStore queries. And sometimes you even have to work against SQL Server by using a MAXDOP 1 to preserve the ordering of your data.