sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

Article
02/27/2023

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

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, value 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: 0 - 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. 1 - 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. 2 - 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. 3 - COMPRESSED - A row group that is compressed with columnstore compression and stored in the columnstore. 4 - TOMBSTONE - A row group that was formerly in the deltastore and is no longer used.
total_rows	bigint	Number of rows physically stored in the row group. For compressed row groups. 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 9 - AUTO_MERGE
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,576 rows. The number of rows could be less if a subset 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 large 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. Note: A partition build with multiple cores can result in more than one trim of this type. 7 - 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 8 - 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 9 - AUTO_MERGE: A Tuple Mover merge operation running in the background consolidated one or more rowgroups into this rowgroup.
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)	1 - NOT_APPLICABLE - the operation does not apply to the deltastore. Or, the rowgroup was compressed prior to upgrading to SQL Server 2016 (13.x) in which case the history is not preserved. 2 - INDEX_BUILD - An index create or index rebuild compressed the rowgroup. 3 - TUPLE_MOVER - The tuple mover running in the background compressed the rowgroup. Tuple mover happens after the rowgroup changes state from OPEN to CLOSED. 4 - 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. 5 - REORG_FORCED - This rowgroup was open in the deltastore and was forced into the columnstore before it had a full number of rows. 6 - BULKLOAD - A bulk-load operation compressed the rowgroup directly without using the deltastore. 7 - 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 where 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 CONTROL permission on the table and VIEW DATABASE STATE permission on the database.

Permissions for SQL Server 2022 and later

Requires VIEW DATABASE PERFORMANCE STATE permission on the database.

Examples

A. Calculate fragmentation 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, remove the deleted rows. For more examples, see Reorganize and Rebuild Indexes.

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))/NULLIF(total_rows,0) 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;

Share via

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

Results

Permissions

Permissions for SQL Server 2022 and later

Examples

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

See Also

Feedback

Additional resources