Table with Clustered Columnstore Index bigger than a Clustered Rowstore Index

Question

We have a query that is doing an index scan on a 36 million row table that has a Clustered Columnstore Index. All the cost of the plan is pretty much on the index scan of this Clustered Columnstore index. I dropped the Clustered columnstore index and replaced it with a Clustered rowstore index, and created a non clustered covering index that covers the query in question. Performance of the query increased from 40 minutes down to a minute. Checking the Properties of the table after adding a Clustered Rowstore index, i noticed that the Data space of the table was greatly reduced. When it still had a columnstore index, Data space was at 34,153MB. After changing it to rowstore index, its now only at 4,163MB. My question is, how is it that the size of this table is bigger when it had a clustered columnstore index when columnstore indexes supposedly compresses the data in a columnstore format? I appreciate anyone that can provide a good link to read on for a similar issue.

Answer 1

Unfortunately, you destroyed all the evidence that could have lead to an answer when you got rid of the CCI.

I'd suspect a lack of maintenance (rebuilds/reorgs) lead to uncompressed rowgroups, or lots of deleted rows leftover in them.

One way to test this would be to create a second copy of the table with a CCI on it and see if it's still larger. A good way to get answers about what's going on in column store indexes generally is NikoNeugebauer's CISL GitHub repo.

• Keith Rivera: Assuming I can reproduce this problem by restoring a backup of the database in a test environment, how would you supposed to troubleshoot it? Are partition schemes going to be a factor as well for this blowup of a size?
• Erik Reasonable Rates Darling: @KeithRivera I'd start with Niko's scripts at the link I posted. If you have questions about the output, post a new question on here.