Qualification Limit for REORGANIZE to remove deleted rows from a rowgroup in Columnstore Index:
By default when one runs ALTER INDEX REORGANIZE the qualifying limit to remove delete rows from a rowgroup is --> the specific rowgroup have 10% of the max possible rows in a rowgroup deleted.
As the max possible rows in a row group is 1 million rows, the qualification limit is 10%, which is 100K rows deleted in a row group before the Reorganize removes them from the rowgroup.
This threshold limit has few concerns
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 .
For example,
drop table TABLE1
--> create table
create table TABLE1
(
roll int,
Name char(10)
)
-->insert 5K rows
declare @i int
set @i=0;
while (@i<5000)
begin
insert into TABLE1 values (@i,'test');
set @i=@i+1;
end
--> Create a columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX [TABLE1_CCIindex] ON dbo.TABLE1 WITH (DROP_EXISTING = OFF)
GO
--> Delte 3.5K rows (70% rows)
delete from TABLE1 where roll<3500
--> checking rowgroup properies
SELECT rg.total_rows,
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,
rg.*
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.