sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Provides current rowgroup-level information about all of the columnstore indexes in the current database.

This extends the catalog view sys.column_store_row_groups (Transact-SQL).

Column name Data type Description
object_id int ID of the underlying table.
index_id int ID of this columnstore index on object_id table.
partition_number int ID of the table partition that holds row_group_id. You can use partition_number to join this DMV to sys.partitions.
row_group_id int ID of this row group. For partitioned tables, this is unique within the partition.

-1 for an in-memory tail.
delta_store_hobt_id bigint The hobt_id for a row group in the delta store.

NULL if row group is not in the delta store.

NULL for tail of an in-memory table.
state tinyint ID number associated state_description.

0 = INVISIBLE

1 = OPEN

2 = CLOSED

3 = COMPRESSED

4 = TOMBSTONE

COMPRESSED is the only state that applies to in-memory tables.
state_desc nvarchar(60) Description of the row group state:

INVISIBLE –A row group that is being built. For example:
A row group in the columnstore is INVISIBLE while the data is being compressed. When the compression is finished a metadata switch changes the state of the columnstore row group from INVISIBLE to COMPRESSED, and the state of the deltastore row group from CLOSED to TOMBSTONE.

OPEN – A deltastore row group that is accepting new rows. An open row group is still in rowstore format and has not been compressed to columnstore format.

CLOSED – A row group in the delta store that contains the maximum number of rows, and is waiting for the tuple mover process to compress it into the columnstore.

COMPRESSED – A row group that is compressed with columnstore compression and stored in the columnstore.

TOMBSTONE – A row group that was formerly in the deltastore and is no longer used.
total_rows bigint Number of rows physical stored in the row group. For compressed row groups, this includes the rows that are marked deleted.
deleted_rows bigint Number of rows physically stored in a compressed row group that are marked for deletion.

0 for row groups that are in the delta store.
size_in_bytes bigint Combined size, in bytes, of all the pages in this row group. This size does not include the size required to store metadata or shared dictionaries.
trim_reason tinyint Reason that triggered the COMPRESSED row group to have less than the maximum number of rows.

0 – UNKNOWN_UPGRADED_FROM_PREVIOUS_VERSION

1 - NO_TRIM

2 - BULKLOAD

3 – REORG

4 – DICTIONARY_SIZE

5 – MEMORY_LIMITATION

6 – RESIDUAL_ROW_GROUP

7 - STATS_MISMATCH

8 - SPILLOVER
trim_reason_desc nvarchar(60) Description of trim_reason.

0 – UNKNOWN_UPGRADED_FROM_PREVIOUS_VERSION: Occurred when upgrading from the previous version of SQL Server.

1 - NO_TRIM: The row group was not trimmed. The row group was compressed with the maximum of 1,048,476 rows. The number of rows could be less if a subsset of rows was deleted after delta rowgroup was closed

2 – BULKLOAD: The bulk load batch size limited the number of rows.

3 – REORG: Forced compression as part of REORG command.

4 – DICTIONARY_SIZE: Dictionary size grew too big to compress all of the rows together.

5 – MEMORY_LIMITATION: Not enough available memory to compress all the rows together.

6 – RESIDUAL_ROW_GROUP: Closed as part of last row group with rows < 1 million during index build operation

STATS_MISMATCH: Only for columnstore on in-memory table. If stats incorrectly indicated >= 1 million qualified rows in the tail but we found fewer, the compressed rowgroup will have < 1 million rows

SPILLOVER: Only for columnstore on in-memory table. If tail has > 1 million qualified rows, the last batch remaining rows are compressed if the count is between 100k and 1 million
transition_to_compressed_state tinyint Shows how this rowgroup got moved from the deltastore to a compressed state in the columnstore.

1- NOT_APPLICABLE

2 – INDEX_BUILD

3 – TUPLE_MOVER

4 – REORG_NORMAL

5 – REORG_FORCED

6 - BULKLOAD

7 - MERGE
transition_to_compressed_state_desc nvarchar(60) NOT_APPLICABLE – the operation does not apply to the deltastore. Or, the rowgroup was compressed prior to upgrading to SQL Server 2016 in which case the history is not preserved.

INDEX_BUILD – An index create or index rebuild compressed the rowgroup.

TUPLE_MOVER – The tuple mover running in the background compressed the rowgroup. This happens after the rowgroup changes state from OPEN to CLOSED.

REORG_NORMAL – The reorganization operation, ALTER INDEX … REORG, moved the CLOSED rowgroup from the deltastore to the columnstore. This occurred before the tuple-mover had time to move the rowgroup.

REORG_FORCED – This rowgroup was open in the deltastore and was forced into the columnstore before it had a full number of rows.

BULKLOAD – A bulk load operation compressed the rowgroup directly without using the deltastore.

MERGE – A merge operation consolidated one or more rowgroups into this rowgroup and then performed the columnstore compression.
has_vertipaq_optimization bit Vertipaq optimization improves columnstore compression by rearranging the order of the rows in the rowgroup to achieve higher compression. This optimization occurs automatically in most cases. There are two cases Vertipaq optimization is not used:
a. when a delta rowgroup moves into the columnstore and there are one or more nonclustered indexes on the columnstore index - in this case Vertipaq optimization is skipped to minimizes changes to the mapping index;
b. for columnstore indexes on memory-optimized tables.

0 = No

1 = Yes
generation bigint Row group generation associated with this row group.
created_time datetime2 Clock time for when this rowgroup was created.

NULL – for a columnstore index on an in-memory table.
closed_time datetime2 Clock time for when this rowgroup was closed.

NULL – for a columnstore index on an in-memory table.

Results

Returns one row for each rowgroup in the current database.

Permissions

Requires these permissions:

  • CONTROL permission on the table.

  • VIEW DATABASE STATE permission on the database.

Examples

A. Calculate fragmentaton to decide when to reorganize or rebuild a columnstore index.

For columnstore indexes, the percent of deleted rows is a good measure for the fragmentation in a rowgroup. When the fragmentation is 20% or more we recommend removing the deleted rows. For examples, see Columnstore Indexes Defragmentation.

This example joins sys.dm_db_column_store_row_group_physical_stats with other system tables and then calculates the Fragmentation column as an estimate of the efficiency of each row group in the current database. To find information on a single table remove the comment hyphens in front of the WHERE clause and provide a table name.

SELECT i.object_id,   
    object_name(i.object_id) AS TableName,   
    i.name AS IndexName,   
    i.index_id,   
    i.type_desc,   
    CSRowGroups.*,  
    100*(ISNULL(deleted_rows,0))/total_rows AS 'Fragmentation'  
FROM sys.indexes AS i  
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 object_name(i.object_id) = 'table_name'   
ORDER BY object_name(i.object_id), i.name, row_group_id;  

See Also

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Querying the SQL Server System Catalog FAQ
sys.columns (Transact-SQL)
sys.all_columns (Transact-SQL)
sys.computed_columns (Transact-SQL)
Columnstore Indexes Guide
sys.column_store_dictionaries (Transact-SQL)
sys.column_store_segments (Transact-SQL)