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.


 


From <https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?vie…>


 


This threshold limit has few concerns


 



  1. 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.

  2. 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_rowsas Decimal(6,3)) AS PercentFull,


    100cast(100.0*(total_rows – ISNULL(deleted_rows,0))/iif(total_rows = 0, 1, total_rowsas Decimal(6,3)) as PercentDeleted,


              i.object_idobject_name(i.object_idAS 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


 


HemanthTarra_0-1613118494276.png


 





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





 


 


HemanthTarra_1-1613118494283.png


 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.