SQL 2016: Columnstore row group Merge policy and index maintenance improvements

            

   A Columnstore index contains row groups that can that have a maximum of 1,048,576 rows. A row group can be closed and compressed due to multiple reasons before that maximum of 1,048,576 rows is reached. Ideally we want that row count in each row group to be as close to the maximum as possible in order to get both better compression and in turn better segment elimination as fewer segments would have to be scanned. In SQL Server 2014, if a row group was compressed before it reached the maximum of 1,048,576 rows, there wasn’t a way to see the reason why it was compressed early before it even hit the maximum.

For a more in-depth discussion of why row groups could be compressed before reaching the maximum row limit (“trim reason”), see the post Data Loading performance considerations with Clustered Columnstore indexes

In SQL Server 2016 we expose the row group trim reason via the Dynamic Management View (DMV) sys.dm_db_column_store_row_group_physical_stats as shown below.

The reasons for trim for a clustered Columnstore index include:

 

Trim_reason_desc	Trim Reason
UNKNOWN_UPGRADED_FROM_PREVIOUS_ VERSION	The reason wasn’t provided prior to SQL Server 2016, so it is also not captured for upgraded data.
NO_TRIM	Not trimmed. The row group has 1,048,576 rows.
BULKLOAD	BATCHSIZE specified for bulk insert, or end of bulk insert.
REORG_FORCED	REORG with COMPRESS_ALL_ROWGROUPS = ON which closes every open row group and compresses it into columnar format
DICTIONARY_SIZE	If Dictionary is full, the row group will be trimmed ( 16MB dictionary)
MEMORY_LIMITATION	Memory pressure during index build caused row group to be trimmed
RESIDUAL_ROW_GROUP_INDEXBUILD	Last row group(s) have less than 1 million rows when index rebuilt.

 

Prior to SQL Server 2016, if we wanted to coalesce smaller compressed row groups into larger row groups or if we wanted to reclaim space due to deleted rows, we would have to rebuild the index. This offline index rebuild could be done at the table or the partition level.

For a more in-depth discussion of index rebuild and maintenance in SQL Server 2014 see SQL 2014 Clustered Columnstore index rebuild and maintenance considerations

Consider the two trimmed row group scenarios below:

A. Multiple row groups exist that are compressed and have less than the 1,048,576 row maximum. Here you see the trim reason as BULKLOAD, which means that there was an insert that was greater than 102,400 rows that directly moves that data into compressed row groups.

Looking at the sys.dm_db_column_store_row_group_physical_stats output below, the current state of rows-per row group could be due to multiple sessions (threads) each inserting 125,000 rows or alternatively it could be due to a single INSERT…SELECT which used a parallel plan, with each thread having inserted 125,000 rows.

B. The Columnstore index sys.dm_db_column_store_row_group_physical_stats output below shows several row groups with a significant number of rows deleted. In this scenario, the deleted rows in a compressed segment are logical deletes and not physical deletes, so in order to reclaim space, you have to rebuild the index in versions prior to SQL 2016

 

SQL Server 2016 Merge Policy

SQL Server 2016 introduces the ability to “merge” smaller, eligible row groups into larger row groups. This is achieved by running INDEX REORGANIZE against a Columnstore index in SQL Server 2016

INDEX REORGANIZE

• Moves closed row groups into compressed columnar format
• Merges multiple row groups into larger row groups that fit within the maximum row group size
• Is an online operation unlike a rebuild index which is offline

A row group is “eligible” to be merged if it means any of the following conditions:

• The row group is compressed
• The row group has 10% or more rows deleted
• The row group was NOT trimmed due to a full dictionary

Here are a couple examples of merge policy at work

a. Single Rowgroup (Self) -merge for deleted rows (reclaiming of space) : In the most simplistic case, you don’t even need multiple row groups to be involved in the merge. For example, if a single row group has more than 10% of its rows logically deleted, then it qualifies for a “self-merge” (space is reclaimed by removing the logically deleted rows from the single row group). The below table shows two examples of single-row groups – one that is eligible for self-merge and one that is not.

Row group Size	Deleted Rows	Self-Merge
400,000	120,000	Yes, deleted rows > 10%
150,000	5,000	No

b. Merge across multiple row groups: In the example below, only two row groups are being depicted, but more than 2 row groups can be candidates to be merged as will be shown later in this article.

 

Row group 1 row count	Row group 2 row count	Eligible for Merge?
950,000	920,000	No, because row group 1 combined with row group 2 exceed the row maximum per row group
400,000	500,000	Yes, because row group 1 combined with row group 2 result in 900,000 rows (under the 1,048,576 row maximum)
1,000,000 ( 200,000 deleted)	500,000 ( 300,000 deleted)	Yes, subtracting out the deleted rows, the combined row count across row groups is under the row maximum

 

Tracking Merge Activity:

You can track the merge activity and row group qualification using the Extended Events columnstore_rowgroup_merge_start and columnstore_rowgroup_merge_end events as below:

 CREATE EVENT SESSION [TupleMover] ON SERVER 

ADD EVENT sqlserver.columnstore_no_rowgroup_qualified_for_merge,
ADD EVENT sqlserver.columnstore_rowgroup_compressed,
ADD EVENT sqlserver.columnstore_rowgroup_merge_complete,
ADD EVENT sqlserver.columnstore_rowgroup_merge_start
ADD TARGET package0.event_file(SET filename=N'XeMerge',max_file_size=(10))
GO
Alter EVENT SESSION [XeMerge] ON SERVER  State = START
 

 

Now consider the following INDEX REORGANIZE scenario against an existing Columnstore index:

 -- Invoke the REORG command to fire Merge and remove deleted rows
-- and coalesce the smaller rowgroups into a larger rowgroup.
alter index cci_temp on FactResellerSalesXL_CCI_temp reorganize

Looking at the output of the extended events, notice that across all the merged row groups there were 1,000,000 rows and 501,786 deleted rows.

Once the merge operation is done, the output of sys.dm_db_column_store_row_group_physical_stats shows one compressed rowgroup that contains the rows in all the prior merged row groups. The merged row groups will have a state description of TOMBSTONE momentarily until they are cleaned up.

In addition to the new sys.dm_db_column_store_row_group_physical_stats DMV, an operational stats DMV sys.dm_db_column_store_row_group_operational_stats has also been added to SQL Server 2016 which gives us visibility into the frequency of scans and lock contention for partitions and row groups (similar to sys.dm_db_index_operational_stats DMV that exists for regular indexes):

In short, the new Merge functionality in INDEX REORGANIZE simplifies Columnstore index maintenance significantly and other DMV’s add visibility into the internals of columnar indexes. There are several other supportability improvements on the Columnstore front not mentioned in this post several other Extended Events and Perfmon counters added to enable better troubleshooting both clustered Columnstore and updatable non-clustered Columnstore indexes

Denzil Ribeiro ( @denzilribeiro )

Comments

• Anonymous
  August 18, 2015
  The dictionary size limit (16MB) and the max row count (1M) need to be lifted urgently! These limits are far too low in practical data sets. Power pivot does not have the 1M row count limit per segment and a recent Microsoft conference presentation proudly showed how much compression can be gained by having bigger row groups. Please enable SQL Server to do the same!

• Anonymous
  September 28, 2015
  how to repeatable row show in select query. In table transaction ID has primary key.