I have a table with 7409825 rows and a nonclustered columnstore index. I am using dmv sys.dm_db_column_store_row_group_physical_stats to check deleted rows. When I issue a delete on table and check column deleted_rows from the dmv, it is always zero.
Shouldn't it show some values on that column? I was reading that rows on columnstore indexes are not deleted, but marked for deletion and they should appear on column deleted_rows from sys.dm_db_column_store_row_group_physical_stats.
I have no rowgroups in the deltastore, all off them are compressed in the columnstore.
I am using this code for check deleted rows
SELECT OBJECT_NAME(CSRowGroups.object_id)as tab,
CSRowGroups.*
FROM sys.indexes AS i
INNER JOIN sys.objects o
ON (i.object_id = o.object_id)
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups
ON i.object_id = CSRowGroups.object_id AND i.index_id = CSRowGroups.index_id
WHERE o.name = 'TableName'
Just found the answer on this link:
With nonclustered columnstore index, deleted rows go first to the COLUMN_STORE_DELETE_BUFFER, which can be seen on sys.internal_partitions.
They only go to the deleted bitmap when deleted rows exceed 1048576 (and so can be checked on the sys.dm_db_column_store_row_group_physical_stats). They can be moved to the bitmap by the background Tuple Mover thread or by an explicit Reorganize command.
Regards