We all love and use Columnstore Segments, but we all kind of scared of the open Delta-Stores (yes there can be more than 1).
Why would that be?
– because they are uncompressed heaps which most probably do not benefit from the Batch Mode at all, and even more probably slowing it down;
– because there is no way of how we can measure their size definitely;

What can we do about it:
1. Rebuild the Index – this operation will definitely close all open Delta-Store, shuffle the data and after some time (depending on a number of factors) it will present us a fully “Segmented” Table :) It might take long time, depending if we have enough partitioning and are using
2. Wait until we can load more data through the normal API (BULK Load API would create another closed Segment without touching on the Delta-Store), filling out the maximum capacity of a Row Group (1.045.678 Rows)
3. Cry. :)

Reorganize with (COMPRESS_ALL_ROW_GROUPS = ON)

Consider an undocumented (at the moment of the writing) hint COMPRESS_ALL_ROW_GROUPS = ON which so far I understand looks for any open Delta-Stores, closes them as they are, without shuffling any additional data and compresses them immediately.
This is our old good friend Tuple Mover, but which corrects any undesired situations. :)

I consider this to be an extremely valuable hint, which in the case of a bug with Delta-Store allows us to correct the situation or should we be getting a lot of queries hitting an open Delta-Store, to compress it and to remove the pressure.

Final thoughts:
– I am wondering if when loading the data we could take a good usage in order to get the desired Row Group sizes … :)
– Are there any methods to prevent automated Segment creation for BULK Load API processes with more then 102.400 rows…?

While executing this on the new SQL Server 2016 I notice that even with the COMPRESS_ALL_ROW_GROUPS = ON statement, it will also start reshuffling the existing row groups. Has there been any changes to the statement? Is it still possible on SS2016 SP1 to force it to only close the open delta stores with some parameter?

I hope that this message finds you well. In your final thoughts section you asked if there are any methods to insert more than 102399 rows while avoiding the bulk load API. I know that this is an old post, but just in case it’s still useful you can try a MAX_GRANT_PERCENT hint of 0 or writing the query in such a way that the cardinality estimate for the insert is less than 251 rows. In the first case the query executes with a memory grant of 0 KB and in the second case it executes without even asking for a memory grant for the columnstore compression. Of course there can be other consequences to the query if it’s more than just a simple INSERT.