This article is contributed. See the original author and article here.
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
- 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 .
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
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments