Clustered Columnstore Indexes – part 29 (“Data Loading for Better Segment Elimination”)

Continuation from the previous 28 parts, starting from

One of the key performance factors for Columnstore Indexes is Segment Elimination. I have been presenting on this matter for quite a while, but never actually blogged about it.

Every Columnstore Index in SQL Server is separated into collection of Segments with information about the minimum and maximum values contained inside each of the segments is available in DMV sys.column_store_segments.
Every time we run a query against Columnstore Index, should it possible to avoid reading a Segment which does not contain pretended values – it will be done.
To see this in action, just following the setup:

Now let’s see what data distribution is existing between segments of the first column (c1):

Screen Shot 2014-04-16 at 00.10.29 First of all of course we see that data is unsorted (which is as it should be inside Columnstore Indexes where ordering does not exist at all), but then I can clearly see that the number of rows in each of the 5 segments is being very far from being ideal.

Now, let us run a query that will read data from every Row Group besides the very first one on the column C1. For this we shall read values superior 1,100,000 by using the following query:

This way we shall eliminate the first Segments of all columnns besides the C4 & C1.
Now comes the interesting question – how can we get to the point of confirming that Segment elimination actually happened?
We cold use the Extended Events, but I will use a couple of trace flags to show it:

Screen Shot 2014-04-16 at 00.22.44 As you can see on the screenshot the only Segment eliminated was with id = 0.

This functionalities are well-known since SQL Server 2012 and Segment has been described for Nonclustered Columnstore Indexes long time ago.

Getting Better Segment Elimination

The tricky part comes now with SQL Server 2014 to get better Segment elimination, which will allow our Columnstore Indexes Performance – because we shall read only data which is matter thus improving IO performance greatly.

How can we can achieve sorted order in our Columnstore table ?
Every time we rebuild our table data is being shifted between Row Groups as I have shown in previous blog posts…

Well, there is a way 😉
Quite limited right now, but I hope that it’s limitation should be removed in the upcoming patches. I guess that this limitation does not exist in PDW. 🙂

To get ordered data we shall experiment with Contoso Retail DW, my favourite database:

My Setup for comfortable working, since I constantly do changes to my version:

Now we need to drop all unique constraints (Primary & Foreign Keys) of our FactOnlineSales table:

Now let us create our Clustered Columnstore Index:

To investigate the situation with the first column Segments, let us execute the following script:

Screen Shot 2014-04-16 at 00.44.00 We can see situation when data is completely unordered, as in the first example in this article. Should we run any query using C1 as predicate – potentially there won’t be a very effective Segment elimination because the same ranges of data exist in a lot of different Segments, but if we could only get them sorted …

Let us view from a different angle – let’s create a traditional rowstore clustered index, which is sorted and than create a Clustered Columnstore Index:

Now all data is being sorted on our first column (OnlineSalesKey), and so we can create Clustered Columnstore Index, which should you have the sorted order 🙂

To verify the improvement please execute the following script:

These are the results that I do observe on my computer:
Screen Shot 2014-04-16 at 00.58.10Stop! This is not exactly what we were waiting for! This was absolutely not what I have expected! Those maximums and minimums are all going wrong direction! I see some patterns, but … 🙁

Screen Shot 2014-04-16 at 00.53.25

But what if we do the same operation but this time running Clustered Columnstore Indexes creation in MAXDOP 1 mode, using just 1 core thus guaranteeing synchronisation:

Here are the results, and yes we have our first column ordered inside the segments.
Screen Shot 2014-04-16 at 01.04.04
Screen Shot 2014-04-16 at 01.06.42

Now I can see a clear way to get a better Segment elimination on a specific column – create a rowstore clustered index and then a clustered columnstore index. Naturally you will have natural disadvantages, such as worse compression (less repeating data), and of course you will be sorting on 1 column only, but it might be secondary to the advantages of the Segment elimination.

I am definitely opening a Connect Item on this one, since there should not be any good reason for such behaviour of the parallel process for Clustered Columnstore Indexes creation – nobody would dream about creating such index on a 20 Billion Rows table in a single thread.

to be continued with Clustered Columnstore Indexes – part 30 (“Bulk Load API Magic Number”)

11 thoughts on “Clustered Columnstore Indexes – part 29 (“Data Loading for Better Segment Elimination”)

  1. Geoff

    Thanks for the information, this was very helpful and I observed the same behavior. Did you end up filing the Connect issue to improve this behavior for parallel columnstore index creation? If so, would you mind posting the link so I can up-vote it?

    I also made a note on the relevant MSFT page (which does not mention the MAXDOP 1 requirement observed here). It seems they moderate comments, so I’m not sure if it will have shown up yet, but here is the page:

    1. Niko Neugebauer Post author

      Hi Geoff,

      thank you for the comment.
      Here is the Connect to be upvoted:

      Sunil Agarwal has already publicly admitted (during 24 Hours of PASS 2014) that Microsoft has got enough requests on this item and that they are potentially evaluating this improvement.

      Let us hope. 🙂

      1. Geoff

        Thanks for the link; I voted it up and added a comment. I think my ideal behavior would be to have an optional “order by” clause to control the ordering of initial columnstore creation, which would allow us to bypass the original clustered index entirely and would control the ordering of row groups within each partition. But simply preserving clustered index order with parallel columnstore creation would be very valuable even without that additional option.

        I’m liking the new columnstore functionality overall. It was too limited in 2012 to be usable for our workloads, especially in being frequently unable to take advantage of batch mode. However, my initial testing shows 2014 to be far superior in this regard.

        I think that perhaps the next on my wish list is the ability to support clustered columnstore indexes for tables with computed columns. We frequently do things like compute cost as “sales – margin” in order to reduce the footprint on our fact tables; while these things could be done via a view, it would be nice to have the computed column option!

        Thanks again for the detailed series on columnstores.

        1. Niko Neugebauer Post author

          Hi Geoff,

          thank you for your vote and for the kind words – I really appreciate them.

          A very interesting idea on the Order By. 🙂
          It would be a very practical, but of course there would be enough people posting scripts with it’s usage, without any understanding what it really does. I guess Microsoft would prefer us to do CL + CCI combination, since it gives them less features to support and worry about. 🙂

          I am asking myself if Microsoft will join Nonclustered & Clustered Columnstore into TheColumnstore in the next version of SQL Server. When they implement the lookups, there will be an opportunity to join both implementation (ignoring the words Nonclustered & Clustered in T-SQL), and then eventually implement some kind of extra ordering. 😉

          Regarding Columnstore & Computed Columns – I keep hearing people requesting it and I personally guess that it is just a question of time, which means next version of SQL Server. They did awesome job improving Data Type support in SQL Server 2014, but it is still far from being complete.

  2. Paul

    Afternoon Niko,

    Unless I am missing something I was wondering how you managed to build a unique clustered index followed by a Clustered ColumnStore Index without dropping the former clustered index.

    I assumed a drop was in order therefore I did, but found that the data is not actually physically sorted, i.e.
    Select 1000 * from with cluster index are in order, dropping the index reverts back to a random order.
    Subsequent segment analysis shows overlapping of min_data_id max_data_ids.

    1. Geoff


      The combination of (DROP_EXISTING = ON, MAXDOP = 1) may provide the functionality you are looking for. DROP_EXISTING allows you to build a clustered columnstore index directly from a clustered rowstore index of the same name, without dropping that index. Doing so in a single-threaded manner retains the order of the original clustered rowstore index.

      1. Paul

        Morning Geoff, appreciate the quick response.

        Yes, that has done the trick, but isn’t this functionality a little naughty, allowing the replacing of a Clustered Index with a Clustered Columnstore Index, and without any warning. The index naming may also become unclear as I tend to prefix a Clustered Columnstore with CCI. I suppose the Clustered Index is only temporary.
        My immediate concern now is how this affects our plan to justify moving to 2016, i.e. if we implement this workaround on 2014, how does 2016 behave with this scenario, will it for instance be tighter and not allow different index types to override each other. Are we just building technical debt. I hope the sorting issue is resolved in 2016.
        We are still in the early stages of looking at benefits, I am particularly interested in the performance boost on windows and analytical aggregation functions (detailed by some of the later articles on this site).

        One observation – I assume segment elimination is only going to be available on the first column due to left to right column inclusion/scanning. We filter heavily on 3 separate columns – just a date or just time and demographic with a wide date range. I have now seen segment elim on Date as the first column and using Batch mode, time and/or demographic on the other hand (with a wide date range) reverts back to row execution mode (though adding a group by invokes the Batch performance boost)
        BTW – forgot to add to my original post – excellent site.

        1. Geoff Patterson

          Yes, it’s a little bit awkward that the CI is replaced by a CCI. The Books Online documentation for DROP_EXISTING explicitly says that you cannot go from CCI => CI, so I guess in order to go back you would need to drop the index.

          Precise control of the clustered columnstore build sort order is not resolved yet in SQL 2016, at least to my knowledge. Perhaps in a future update, we’ll see. However, I would expect your issue of seeing row mode used unexpectedly is likely addressed in 2016 as batch mode has been a big focus of development.

          In terms of multi-dimensional segment elimination, I’m not sure there are any perfect options. But here are a few ideas:

          * If the data set is quite large, you can potentially partition by date and then sort your data by a different column in order to have partition elimination on date and segment elimination (within each partition) on a secondary column. Just make sure that the partitions are wide enough that each partition has millions of rows (enough for multiple segments within the partition).
          * Alternatively, if read performance is absolutely crucial and the data is mostly read-only, you could build multiple copies of the table and sort each one for different segment elimination. You queries would then need to know which one to target, which might be a pain.
          * You could also experiment with b-tree non-clustered indexes on one or two of the dimensions that are not optimized for segment elimination. It seems that tables that mix a CCI and additional non-clustered indexes are well supported in SQL 2016. How successful this would be probably depends on how selective your queries are on those additional dimensions.

          1. Paul

            Thanks, I really appreciate your time. Will have to look at building additional indexes once we have a 2016 environment to play with.

        2. Niko Neugebauer Post author

          Hi Paul,

          the DROP_EXISTING hint works correctly, as I interpret it – replacing a Clustered Rowstore Index with another type of a Clustered Index.
          The naming convention can be one big pain, but you can easily correct it with the sp_rename function.

          The functionality you are asking (sorted rebuilds) is definitely coming and there are already error messages to be found within the core of SQL Server 2016, but at the RTM it is not enabled yet. Let us hope that it will be released very, very soon.
          In the mean time, consider doing this trick manually or with the help of the maintenance solution within CISL (open source Columnstore Library –

          Regarding the multiple segment elimination – it works out of the box. You can do sorting on 2 columns, with the help of the technic, described here :

          Best regards,
          Niko Neugebauer

Leave a Reply

Your email address will not be published. Required fields are marked *