January 28, 2020 at 10:04 pm
All,
I am working on implementing table partitioning on a existing table. The table has clustered columnstore index.
I have to drop the index and recreate it to use the partition scheme
The table size before dropping the index is 200 GB
and after dropping the index the table size is 4 TB
Do you know why the size of table got increased after dropping the index?
Thanks
January 28, 2020 at 10:45 pm
Compression. Columnstore indexes can use columnstore or columnstore archival compression which significantly reduces the space used.
Sue
January 28, 2020 at 10:49 pm
perfectly normal and expected
Columnstore compression is very high - when you dropped it the table got converted to a heap with no compression ( or with standard page compression) so the size increase is normal
I've never done partitioning with a columnstore so I can only suggest that you create an empty table with the required partitioning and do inserts into it instead of dropping and recreating the new index.
may be faster and should, hopefully, take a lot less space
January 29, 2020 at 12:27 pm
Just a caution. Partitioning is great for data management. Partitioning is horrible for performance. It only enhances performance when you see a near perfect ability of your code to always achieve partition elimination. No partition elimination, then partitioning actively hurts performance. Make sure you're implementing it for the right reasons.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply