January 20, 2017 at 9:44 am
We are doing some testing on converting some of our largest tables (anything 200M - 7B rows) to clustered columnstore tables to gain the impressive compression and then adding a non-clustered index if necessary for specific queries. What I'm noticing though is that while I might get a 90% space reduction from converting the clustered index to a clustered columnstore, as soon as I add a non-clustered index (even with just two keys and no included columns) the index size is bringing the total table size back up so I'm really only saving about 10-20% of the size on disk.
Can anyone help me with why that is? I haven't been able to find any good documentation on this scenario. I'm theorizing that it is because a non-clustered index is typically built on the backbone of the clustered index...and in this case, there is no rowstore clustered index and therefore it is creating a full b-tree of all columns instead of a concatenated version with just the columns. But I could be way off base.
One example is a table that has seven columns, all INT, TINYINT or MONEY with 2.3B rows in it. Original size on disk was 80GB, new size with only clustered columnstore is 7.8GB (90.4% savings), then when adding a non-clustered index on just three of the INT columns (to kind of simulate the prior primary key values), the Index Space alone shoots up to 41.3GB. So the total new size is now 49.15GB (39.3% savings).
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply