Clustered Columnstore Indexes – part 22 (“Invisible Row Groups”)

Continuation from the previous 21 parts, starting from https://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/

Last week on an amazing 2013 edition of PASSCamp in Germany one of the attendees has called my attention to the fact that during hands-on labs he has managed to get an invisible Row Group. I was in the middle of demos and did not get the message during the first call – I thought that it was an unexpected Row Group that has managed to see, but as a matter of a fact – it was something different.

Indeed it was something, that is called Invisible Row Group. I can only say that I was extremely excited about it since I have never managed to see one in my tests and demos. I am referring to the very special moment of life of any Delta Store, when it is right to become a compressed Segment. If you are running tests during the period when Tuple Mover kicks in, you have great chances to find it at CTP2 for sure.

When I had a first couple of minutes free I have managed to imitate the conditions, which I will describe below:
Lets kick off by creating an empty table with a Clustered Columnstore Index:

create table dbo.MaxDataTable(
	c1 bigint,
	c2 numeric (36,3),
	c3 bit,
	c4 smallint,
	c5 decimal (18,3),
	c6 smallmoney,
	c7 int,
	c8 tinyint,
	c9 money,
	c10 float(24),
	c11 real,
	c12 date,
	c13 datetimeoffset,
	c14 datetime2 (7),
	c15 smalldatetime,
	c16 datetime,
	c17 time (7),
	c18 char(100),
	c19 varchar(100),
	c20 nchar(100),
	c21 nvarchar(100),
	c22 binary(8),
	c23 varbinary(8),
	c24 uniqueidentifier,
);

-- Lets try out to create a Clustered Columnstore Index:
Create Clustered Columnstore Index 
	CC_MaxDataTable on dbo.MaxDataTable;

I will insert 2 new rows and compress this Row Group:

insert into dbo.MaxDataTable
	default values;

insert into dbo.MaxDataTable
	default values;

alter table dbo.MaxDataTable
        rebuild;

Let us insert 10.000 rows and update them immediately:

-- Insert 10.000 rows
insert into dbo.MaxDataTable
	default values;
GO 10000

-- *****************************************************************
-- Lets Update all rows with some data 
with updTable as
	(
	select *
		, row_number() over(partition by C1 order by C1) as rnk
		from dbo.MaxDataTable
	)
update updTable
	set C1 = rnk,
		C2 = ABS(CHECKSUM(NewId())) % 142359.0,
		C3 = 1,
		C4 = cast(ABS(CHECKSUM(NewId())) % 10000 as smallint),
		C5 = ABS(CHECKSUM(NewId())) % 242359.0,
		C6 = rnk / ABS(CHECKSUM(NewId())) % 242359.0,
		C7 = ABS(CHECKSUM(NewId())) % 2000000,
		C8 = ABS(CHECKSUM(NewId())) % 255,
		C9 = rnk / ABS(CHECKSUM(NewId())) % 242359.0,
		C10 = rnk / ABS(CHECKSUM(NewId())) % 242359.0,
		C11 = rnk / ABS(CHECKSUM(NewId())) % 242359.0,
		C12 = getDate(),
		C14 = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),
		C15 = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),
		C16 = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),
		C17 = getDate(),
		C18 = cast( ABS(CHECKSUM(NewId())) % 242359.0 as char(25)),
		C19 = cast( ABS(CHECKSUM(NewId())) % 232659.0 as varchar(25)),
		C20 = cast( ABS(CHECKSUM(NewId())) % 242359.0 as nchar(25)),
		C21 = cast( ABS(CHECKSUM(NewId())) % 232659.0 as nvarchar(25));

Before we advance to the next step, please use the following script to monitorize the state of the Row Groups in our table:

SELECT rg.total_rows, 
	    cast(100.0*(total_rows)/1048576 as Decimal(6,3)) as PercentFull,
		cast(100-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) = 'MaxDataTable' 
		--and state = 0
	ORDER BY object_name(i.object_id), i.name, row_group_id;

Invisible

Its time to insert an extra Row Group – 1.045.678 rows:

-- Insert 1.000.000 rows in order to get the Delta-Store full )
declare @i as int;
declare @max as int;
select @max = isnull(max(C1),0) from dbo.MaxDataTable;
set @i = 1;

begin tran
while @i <= 1048576
begin
	insert into dbo.MaxDataTable
		default values

	set @i = @i + 1;
end;
commit;

You should immediately kick in Tuple Mover manually, while checking the status of the Row Groups using the script above:

alter index [CC_MaxDataTable] on dbo.MaxDataTable
	reorganize;

This is a screenshot of the result that I have managed to capture:

There are 4 Row Groups visible, with 1 of them having status equal to 0 (Zero), and status_description of "Invisible". This is clearly a moment when we are compressing our closed Delta-Store which is identified with Row_Group_id = 1 (state = "closed").

How can I be sure ? - Very easily: should we keep executing our script for checking on Row Groups, we should be able to find our Row Group with id = 2 gone:

A very clear show of how things are functioning in Clustered Columnstore :)

This raises another important issue for all those future scripts consulting sizes of Clustered Columnstore Indexes - a predicate with state > 0 should be included in order to avoid double counting the number of rows inside the Clustered Columnstore Index.

to be continued with Clustered Columnstore Indexes – part 23 ("Data Loading")