Continuation from the previous 8 parts, starting from https://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/
I have been playing with the internals of the master database of the SQL Server 2014 CTP1 and hence have discovered a couple of interesting things:
– While Replication will not be supported, it seems that the Availability Groups are still on the table and should be included in the final release. Comment: I definitely can live with this limitation, since AG are definitively taking over the high availability. I have not seen anything Columnstore Related in the Availability Groups support and so I am impatiently waiting for the CTP2.
– Judging by the code inside of the system Stored Procedures, Clustered Columnstore Indexes will have no traditional statistics which can be consulted. Comment: Nonclustered Columnstore Indexes have had none as well, and so mainly there is no surprise, but still I would love to be able to see & understand them better.
– Microsoft is definitively not excluding the option of the Hekaton expanding to support the Clustered Columnstore Indexes one day. There is at least one direct reference which is left in the sort of the comment that there might be this expansion one day. Comment: I believe that supporting Hekaton for Delta Stores might make a good difference in a number of scenarios, especially for the mixed environments, where worlds of OLAP and OLTP collide.
I understand that Bulk Loading can provide a good amount of solutions, especially since we could potentially load them into a temporary staging table before loading into Clustered Columnstore Index, but having at least a Delta Store which is not limited by the latches and locks (see https://www.nikoport.com/2013/07/07/clustered-columnstore-indexes-part-8-locking/) might be a very good advantage for a number of scenarios.
– The way the execution of Columnstore operations is working, should be based on the OPENROWSET operator – there are enough references with the TABLE COLUMNSTORE_ROW_GROUPS or table COLUMNSTORE_TUPLE_MOVER or even TABLE ALUCOUNT, for example. Comment: Clustered Columnstore operations in a lot of cases should be executed in the Batch (Bulk) mode so it make sense that they are somehow connected with OPENROWSET operation, but I would love to understand what those references mean and how can we better use them.
– The procedure [sp_estimate_data_compression_savings] which serves for the compression estimation is still does not have any references to the Columnstore Indexes. Comment: I hope that this function will get updated before the RTM. We can already manage the storage compression inside of the GUI for Clustered Columnstore Indexes, but most of the DBA’s will definitely need that the Stored Procedures will be able to deliver the estimation. One should not advance with compression of a 400 GB table without having any estimation from the engine of how this is expected to turn out.
to be continued with Clustered Columnstore Indexes – part 10 (“Compression basics”)
once again, really interesting.
I still had no time fo a deep-dive myself, so I can watch you do it for me ;-)
Thanks Andreas, I am looking forward to the time you will find to dive into Clustered Columnstore Indexes. I find this topic highly fascinating. :)
Pingback: Clustered Columnstore Indexes – part 1 (“Intro”) | Nikoport
The support for ColumnStore compression will be added in SQL 2019 to the sp_estimate_data_compression_savings procedure – see
thank you. I have also blogged 2 times on this topic:
Columnstore Indexes – part 124 (“Estimate Columnstore Compression”)
Columnstore Indexes – part 125 (“Estimate Columnstore Compression as a System Stored Proc”)
You might find some info there interesting.