How to get rid of thousands of TOMBSTONE rowgroups in Clustered Column Store Index

Question

Hi,
Have a SQL Server 2017 database containing a big table (~ 400.000.000 rows) using Clustered Column Store Index.
Had some performance problem reading from the table and started to investigate the cause.
Found that I had a configuration-problem with my daily indexing jobs which resulted in this clustered column store index not being reorganized for a very long time.
Because of this I ended up with a huge number of row-groups (> 470.000). In a perfect world I guess it should only be like 400 rowgroups.
I manually fired reorganize index (ALTER INDEX cci_myindex ON dbo.myTable REORGANIZE WITH (LOB_COMPACTION = ON)) which took around 5 hours to complete.
After that I ended up with most of the row-groups in TOMBSTONE state (which I guess is correct).
However the problem is that I can´t get rid of these TOMBSTONE rowgroups. It seems like a "background process" removes row-groups, but it takes very long time. The number of rowgroups decreases, but very slowly. If it continues like this it will take like half a year before all TOMBSTONEd rowgroups are cleaned up.

Is there a way to remove the TOMBSTONE rowgroups faster?
The table still have poor read performance.

Regards
/ Steve

Answer 1

Hi @Stefan ,

A rowgroup that is labelled with the state TOMBSTONE indicates that it was a part of a deltastore and was forcefully compressed into a columnstore rowgroup. The residual rowgroup no longer has any non-deleted rows in it and therefore is labelled with the ominous tombstone state. These are intermediary rowgroups that will be deallocated. We can force this along with another reorganization command, and the tombstone rowgroup is gone.

Please refer to this blog Hands-On with Columnstore Indexes: Part 3 Maintenance and Additional Options to get more information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2

Thanks for the answer @CathyJi-MSFT .

I tested this a couple of days ago, to do an additional reorg. My first reorg took about 5 hours, the second one (trying to get rid of tombstone, did the same command "REORGANIZE WITH (LOB_COMPACTION = ON)") runned for 15 hours before I stopped the process. Is this as expected, that the second reorg takes so long time? I´m not sure if it will succeed if I wait more time. Or is there any other good approach? I did some testing today trying to create a new table (a copy of the other one) and move the data from the old table to the new one. But the performance of the original table is so poor so this will also take like forever. SELECT top 1000000 rows (ordered by the pk id) from the original table takes like 40 minutes. Another approach I was thinking about is to drop and re-create the index, but I´m not sure how long this will take. Any ideas?

Answer 3

Hi @Stefan ,

Sorry for reply late. According MS document, the tuple mover is a background process that deallocates any rowgroups in which every row has been deleted. Deallocated rowgroups are marked as TOMBSTONE. To run tuple mover immediately, use the REORGANIZE option of the ALTER INDEX statement. It also suggest we using reorganization option to remove TOMBSTONE rowgroups. Could you please restart your SQL server service if possible and do this again?

> I was thinking about is to drop and re-create the index, but I´m not sure how long this will take

It seems re-create the index will spend more time.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 4

Hi @CathyJi-MSFT ,
Thank you for your answer.
I tested to restore the database into another server. I run the reorg for almost 5 days there without finishing. Then I restarted the SQL Server service (as you suggested) and started reorg again. It run for 18 hours without finishing before I killed the process. Still over 450.000 rowgroups, most in TOMBSTONE state.

Best Regards
Stefan