Columnstore Indexes – Catalog Views – Welcome to our SQL World!

In this article, we will first take a look at how to recognize columnstore indexes in some of the generic catalog views. After that we will investigate the new catalog views that have been added just for columnstore indexes.

sys.indexes and sys.index_columns

We can find information about columnstore indexes in two generic catalog views: sys.indexes and sys.index_columns.

SELECT
       OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       i.index_id,
       i.type,
       i.type_desc,
       i.is_unique,
       i.ignore_dup_key,
       i.is_primary_key,
       i.is_unique_constraint,
       i.fill_factor,
       i.is_disabled,
       i.has_filter
FROM
       sys.indexes AS i
WHERE
       i.type IN (5, 6) -- 5 = Clustered columnstore index. 6 = Non-Clustered columnstore index.

sys.column_store_segments

sys.column_store_segments provides detailed information about the individual segments within the columnstore index.

SELECT    
       OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       i.type_desc AS IndexType,
       COALESCE(c.name, '* Internal *') AS ColumnName,
       s.segment_id,
       s.row_count,
       s.has_nulls,
       s.on_disk_size
FROM      
       sys.column_store_segments AS s
       INNER JOIN sys.partitions AS p
       ON p.hobt_id = s.hobt_id
       INNER JOIN sys.indexes AS i
       ON i.object_id = p.object_id AND i.index_id = p.index_id
       LEFT JOIN sys.index_columns AS ic
       ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.index_column_id = s.column_id
       LEFT JOIN sys.columns AS c
       ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE     
       i.type IN (5, 6) -- 5 = Clustered columnstore index. 6 = Nonclustered columnstore index.
ORDER BY
       TableName, IndexName, s.column_id, p.partition_number, s.segment_id;

Column Name	Description
segment_id	ID of the rowgroup. For backward compatibility, the column name continues to be called segment_id even though this is the rowgroup ID. You can uniquely identify a segment using <hobt_id, partition_id, column_id>, <segment_id>.
row_count	Number of rows in the row group.
has_nulls	1 if the column segment has null values.
on_disk_size	Size of segment in bytes. The on_disk_size column, when used in conjunction with row_count, can be used to quickly see how well (or how bad) the data in a segment was compressed.

A non-clustered columnstore index will sometimes contain additional columns that were not included in the CREATE INDEX statement. If you have not explicitly included all the columns of the table’s clustered (row-store) index, SQL Server will still add all of them to the non-clustered columnstore index. Additionally, if the table’s clustered index is a non-unique index, SQL Server will have created a hidden unique identifier that you cannot explicitly specify, but will still be added to the columnstore index. And if the table has no clustered index at all, SQL Server will add a hidden column for the RID (or Row ID, which is the location of the row in the heap). Data for each of these columns is included in sys.column_store_segments, but not in sys.index_columns. The outer join to sys.index_columns ensures that these “hidden columns” in sys.column_store_segments are still exposed.

sys.column_store_row_groups

sys.column_store_row_groups provides columnstore index information on a per-segment basis to help the administrator make system management decisions. sys.column_store_row_groups has a column for the total number of rows physically stored (including those marked as deleted) and a column for the number of rows marked as deleted. Use sys.column_store_row_groups to determine which row groups have a high percentage of deleted rows and should be rebuilt.

SELECT   
       OBJECT_NAME(rg.object_id) AS TableName,
       i.name AS IndexName,
       i.type_desc AS IndexType,
       rg.row_group_id,
       rg.state,
       rg.state_description,
       rg.total_rows,
       rg.deleted_rows,
       rg.size_in_bytes
FROM      
       sys.column_store_row_groups AS rg
       INNER JOIN sys.indexes AS i
       ON i.object_id = rg.object_id AND i.index_id = rg.index_id
WHERE     
       i.type IN (5, 6) -- 5 = Clustered columnstore index. 6 = Nonclustered columnstore index.
ORDER BY  
TableName, IndexName, rg.partition_number, rg.row_group_id;

Column Name	Description
row_group_id	The row group number associated with this row group. This is unique within the partition.
state	ID number associated with the state_description. 0 = INVISIBLE 1 = OPEN 2 = CLOSED 3 = COMPRESSED 4 = TOMBSTONE
state_description	Description of the persistent state of the row group: INVISIBLE –A hidden compressed segment in the process of being built from data in a delta store. Read actions will use the delta store until the invisible compressed segment is completed. Then the new segment is made visible, and the source delta store is removed. OPEN – A read/write row group that is accepting new records. An open row group is still in rowstore format and has not been compressed to columnstore format. CLOSED – A row group that has been filled, but not yet compressed by the tuple mover process. COMPRESSED – A row group that has filled and compressed. TOMBSTONE – The tuple mover also deallocates any rowgroups in which every row has been deleted. Deallocated rowgroups are marked as TOMBSTONE.
total_rows	Total rows physically stored in the row group. Some may have been deleted but they are still stored. The maximum number of rows in a row group is 1,048,576
deleted_rows	Total rows in the row group marked deleted.
size_in_bytes	Size in bytes of all the data in this row group.