for few groups which are of full size of 1M rows, reaching 100K deleted rows may take a long time and until then the deleted rows are not cleared.
For smaller rowgroups, the percentage of deleted rows will be huge but still the deleted rows will not be considered done.
New Trace flags from SQL 2019 CU9:
From SQL 2019 CU9 we have introduced 2 new trace flags for better management of the deleted rows.
Trace flag 11631 --> will not use the ~1M rows to calculate the 10%, but rather it will use the actual no of rows in a rowgroup.
Therefore if your rowgroup has only 20000 (20 k) rows, the limit comes to 10% of 20000 i.e to 2000 deleted rows, Thus, if have >= 2k rows deleted REORG will consider this rowgroup for cleanup of deleted rows
Trace flag 11634 --> will bring down the Percentage of deleted rows limit from 10% to 1%. If enabled together with trace flag 11631 then it will be 1% of the current number of rows in a rowgroup, instead of 1% of 1 million rows .
drop table TABLE1
--> create table
create table TABLE1
-->insert 5K rows
declare @i int
insert into TABLE1 values (@i,'test');
--> Create a columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX [TABLE1_CCIindex] ON dbo.TABLE1 WITH (DROP_EXISTING = OFF)
--> Delte 3.5K rows (70% rows)
delete from TABLE1 where roll<3500
--> checking rowgroup properies
cast(100.0*(total_rows - ISNULL(deleted_rows,0))/iif(total_rows = 0, 1, total_rows) as Decimal(6,3)) AS PercentFull,
100-cast(100.0*(total_rows - ISNULL(deleted_rows,0))/iif(total_rows = 0, 1, total_rows) as Decimal(6,3)) as PercentDeleted,
i.object_id, object_name(i.object_id) AS TableName,
i.name AS IndexName, i.index_id, i.type_desc,
FROM sys.indexes AS i
INNEr JOIN sys.column_store_row_groups AS rg
ON i.object_id = rg.object_id
AND i.index_id = rg.index_id
WHERE object_name(i.object_id) = 'TABLE1'
ORDER BY object_name(i.object_id), i.name, row_group_id;
--> Even though 70% of the rows are deleted, the REORGANIZE won’t consider this rowgroup for cleanup of the deleted rows.
ALTER INDEX [TABLE1_CCIindex] ON dbo.TABLE1 REORGANIZEàhas no affect and the deleted rows wont be cleared
dbcc traceon(11631,-1) --> The threshold limit is now calculated for the 5K rows but not 1M rows. i.e So the limit comes down to 10% of 5K i.e >= 500 deleted rows.
dbcc traceon(11634,-1) --> Brings down the threshold limit further from 10% to 1% i.e from 500 to 50 deleted rows.
Now after the Trace flags are enabled, when we run the
ALTER INDEX [TABLE1_CCIindex] ON dbo.TABLE1 REORGANIZE --> effectively cleans up the deleted rows