SQL 2016 SP1
Hardware: 8 cores
Memory: 128 GB
Disk: All SSD's
Network: very fast
I know that I should post DDL and sample data but I am curious about other peoples experiences with clustered columnstore table structures. I don't think this is a DDL or indexing issue.
We have some fairly wide and tall tables that are used for populating cubes. 700+ Million rows. Almost all VARCHAR(255) columns. They have been traditional rowstore objects but we converted some to clustered columnstore. The read performance was stellar. It actually reflected some of the marketing info Microsoft puts out regarding performance gains.
Unfortunately, we had to make changes to some of the data and this happens weekly and monthly in some cases. Updating values in batches of 20 million rows ran significantly slower than traditional rowstore tables. The table is also partitioned and the updates were being run against a single partition (out of 30 or so) at any time.
In the end, we removed the clustered columnstore index, created specific indexes for the updated statements, ran the updates, removed the task specific indexes, then recreated the clustered columnstore.
Are the performance issues with updates a common issue with columnstore tables?
I have read anything I can get my hands on regarding this and have not found a satisfying explanation. One of the best resources I found was this Site
We are about to load 20 Billion rows using columnstore and the data will have updates daily. If modifications are so painful, this may not be the solution we hope it will be.
I appreciate your time and consideration.