Why not create ColumnStore index on every single table?

Question

As ColumnStore index improves performance for data warehouse databases, and also since SQL 2104, it is updatable too, why not creating ColumnStore index on every single table?

Edit: In SQL 2014 we cannot have any other index if there is a CLUSTERED ColumnStore index. This limitation is eliminated in SQL 2016 so my question can be more relevant for SQL 2016.

Answer 1

In SQL Server 2016, if you have & processing over million of rows, the only thing that would not allow you to use Columnstore Indexes will be the current technology limitations, such as Replication on CCI, CLR, CDC or CT over CCI and so on ... Otherwise this should be a default way to approach any modern Data Warehouse.

Answer 2

Why? Because the performance would be awful.

Microsoft marketing literature is to get people to buy the product, it's not for you to read every line and take it on faith that this is exactly how the product will work in every circumstance. So when you see fantastic claims about disk space reduction and 40x IMOLTP speed increases that's when your alarm bells should go off.

It's not that they're lying or anything. CS and IMOTLP are awesome for the extremely specific niche cases they use as case studies. It's just that these benefits do not translate to non-niche use cases.

Disclaimer: I'm not an expert on CS like Niko, and ditto with IMOLTP. I don't work on massively concurrent high performance with millisecond latency. I'm just a normal DBA.

But I did try to implement them in a small reporting database project I was working on earlier in the year because I had the opportunity to and thought it might give that massive 10x disk 40x CPU benefit.

After getting my initial structure up and running, and converting to IMOLTP and later to CS I saw performance drops across the board, big ones. I spent a week or two reading through documentation and tweaking the settings but in the end b-trees were the fastest.

I imagined that IMOLTP would give 10-40x performance increase because the data was kept in memory and in a different structure. However it turns out it doesn't make any difference for small tables that would be in the buffer pool anyway. Those benefits were ones that only come from high concurrency or using native procedures (which I couldn't, my logic was 6-7 nested CTEs with lots of window functions).

I imagined that I'd be able to wrangle performance from the CS also but it turns out they're terrible at range scans, exactly as they state somewhere in the documentation, and if you're doing reporting or analysis then those are pretty darned common.

So yeah they are not drop-in replacements for anything and sometimes reading is not enough. Do your own testing and form some of your own conclusions.

Answer 3

Data warehouses generally have two performance issues. Inserting data and reading data. One tries to avoid updating or deleting data to avoid the excessive times it takes. Columnstore indexes work best on tables with at least 1 million rows per partition. They also work best on compressible columns. A highly selective column is not compressible whereas a highly non-selective column is very compressible. Inserts are slowed down with CS indexes, which just so happens to be where most DW's pain exists. As one gentleman indicated, MS tends to over market their features and quite often releases things that were not ready to be released. In SS 2016, CS indexes are almost ready to be released. If you have a table with 1 billion rows, which would be better suited for a columnstore if the table is compressible, it might take you 40 plus hours just to drop the existing clustered index. Often DBA's spend much time moving data out of the table, just so they can drop the clustered index and then add a columnstore, just to find out it is a detriment to insert time. There is often a lot of time lost in testing to find that it won't work for your solution. CS indexes are hardly anyone's savior. You would better be concerned with your partitioning and parallelization. And maybe one day a decent CS scheme will be release. Hopefully by then you won't have too many rows to take advantage of them. Temp tables with a lot of columns is a good place to utilize CS. Sometimes they can provide good benefits there. There are some tricks to hustle the CS to force batching too, but it won't force batching at the table level if it doesn't have a columnstore. It will only batch aggregation, sorts and other similar operations. Also, if your partitions are not evenly matched (for example by partition by accounting period would be most evenly matched), the columnstore may be advantageous for some partitions while providing a disadvantage for others.