Clustered Column Store: Factors that impact size of a RowGroup

[SQL-Server-Team]

First published on MSDN on May 03, 2014

Clustered Column Store: Factors that impact size of a RowGroup:

A clustered column store index stores rows in columnar storage format in group of rows, referred to as rowgroups. There are two types of rowgroups as follows:

• Delta rowgroup – stores data in traditional row storage format.


• Compressed rowgroup:  stores rows in columnar storage format to get high degree of compression.

The data compression achieved by columnar storage depends upon the number of rows in the rowgroup. In our experimentation, we have found that any rowgroup with 102400+ rows can achieve good column compression. Based on this, the clustered column store index follows these guidelines

• When loading 100k+ rows per batch into column store, the rows are directly loaded as compressed rowgroup


• Regular inserts loads rows into delta rowgroup. When the number of rows reach 1 million (1,048,5766), the rowgroup is closed and converted to columnar storage format by a background thread called tuple mover. Rowgroup with 1 million rows were chosen to strike a balance between too many rowgroups vs too large a rowgroup.

For details please refer to http://msdn.microsoft.com/en-us/library/gg492088(v=sql.120).aspx . Besides the bulk import, the factors that impact the size of rowgroup are

• Degree of parallelism (DOP)


• Size of dictionary


• Memory

Let us walk through each of these factors with examples

Create Index

Here I have a table where I have loaded million rows. Now I create a clustered column store index (CCI) with different DOPs

CREATE TABLE dbo.t_colstore (
c1 int NOT NULL,
c2 INT NOT NULL,
c3 char(40) NOT NULL,
c4 char(1000) NOT NULL)
go

set nocount on
go
-- load 2000000 rows
declare @outerloop int = 0
declare @i int = 0
while (@outerloop < 2000000)
begin
Select @i = 0
while (@i < 2000)
begin
insert t_colstore values (@i + @outerloop, @i + @outerloop, 'a',
concat (CONVERT(varchar, @i + @outerloop), 'b'))
set @i += 1;
end

set @outerloop = @outerloop + @i
set @i = 0
end
go


CREATE CLUSTERED COLUMNSTORE INDEX t_colstore_cci ON t_colstore with (maxdop = 1)

Here is what I see for the rowgroup when querying the catalog view sys.colunm_store_row_groups. Note, you have 1 row group with 1 million rows (i.e. 1024 * 1024) and other rowgroup with < 1 million rows because this was the last rowgroup with < 1 million rows. Here is the output using a new DMV in SQL Server 2016. Key things to note are (a) when creating or rebuilding an index, all rows get compressed even if there are < 1 million. This is represented y 'RESIDUAL_ROW_GROUP' state (b) the operation that caused the compression of row group can be identified as 'INDEX_BUOILD here'

[caption id="attachment_3966" align="aligncenter" width="879"] Row Group Size with DOP = 1[/caption]



Now, let us recreate the index with default DOP (i.e. = 0)

ALTER INDEX t_colstore_cci ON t_colstore REBUILD

[caption id="attachment_3975" align="aligncenter" width="879"] Row Groups with Default DOP[/caption]

Here you see one extra rowgroup. This is because multiple threads (depending upon the number of COREs available) are creating rowgroups and they run out of rows before reaching the million rows. One thing to note is that there is no guarantee that each thread will get equal number of rows.

Dictionary size limitation

As part of column compression, a dictionary (primary/secondary) is used to store commonly occurring  byte patterns. The dictionary is persisted with compression but the  In-Memory size of dictionary is limited to 16MB. The size of rowgroup is automatically reduced if dictionary gets full.  Let us walk through an illustrative example using the same table as before but after drop/recreate. In the data load below, notice the distinct prefix for column C4. This creates separate entries, each containing 950 bytes, causing the dictionary to grow larger than 16MB, which forces smaller rowgroups.

drop table t_colstore
go

CREATE TABLE dbo.t_colstore (
c1 int NOT NULL,
c2 INT NOT NULL,
c3 char(40) NOT NULL,
c4 char(1000) NOT NULL)

-- load 1100000 rows
declare @outerloop int = 0
declare @i int = 0
while (@outerloop < 1100000)
begin
Select @i = 0
while (@i < 2000)
begin
insert t_colstore values (@i + @outerloop, @i + @outerloop, concat(convert(varchar, @I + @outerloop), 'b')
set @i += 1;
end
set @outerloop = @outerloop + @i
set @i = 0
end
go

Now, I create the CCI as follows and check the rowgroups using the catalog view sys.column_store_row_groups and you will see I have 22 row groups instead of 2 and the trim reason is 'DICTIONARY_SIZE'. It is because the in-memory size of the dictionary can't exceed 16 MB.

CREATE CLUSTERED COLUMNSTORE INDEX t_colstore_cci ON t_colstore with (maxdop = 1)

[caption id="attachment_3985" align="aligncenter" width="1752"] Row Group truncation due to dictionary size[/caption]

Now, let us try another variation by loading  the 1.1 million rows through delta row groups and see how many compress row groups we get. Here are the additional rowgroups after loading the data.



Note, both of these are delta rowgroups. The closed rowgroup has 1 million rows as you would expect. Why did this not get split into multiple rowgroups due to dictionary? The simple answer is that dictionary is only relevant in the context of compressed rowgroup. Let us run the REORGANIZE command as follows to force compression of the delta rowgroup.

ALTER INDEX t_colstore_cci ON t_colstore REORGANIZE

Here is the output of the catalog view sys.column_store_row_groups. Note, that the closed 'delta' rowgroup' was broken into smaller rowgroups as part of compression

Memory Limitations

When compressing rowgroups, it is required that all the rows in the rowgroup must fit in-memory including the dictionary. If low memory is detected, the server closes the current segment even if the segment row count does not reach 1 million (i.e. leading to smaller sized rowgroup). As part of memory pressure, the index create/rebuild automatically adjusts the DOP to enable successful completion of index create/rebuild.

Thanks
Sunil