0

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.

2

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.

  • 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? – Keith Rivera Sep 24 '19 at 20:35
  • 1
    @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. – Erik Darling Sep 24 '19 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.