How to view properties of a SQL Server Column Store Clustered/ Non- Clustered Index?

Problem: How to view properties of a SQL Server Column Store Clustered/ Non- Clustered Index?

Solution:

Before jumping into properties and details about the column store index statistics and usages, let me give a quick definition of what a column store index is and what it does.

Column Store Indexes are indexes which are created in a columnar format based on a column/s. Please go through this article. what is a Column Store index?,

Once a clustered/ non- clustered column store indexes are created there are some object catalog views which store this information based on the properties that are specified while creating an index.

Below are some important views that can help in retrieving segments, storage and types of column used in the column store:

1.       sys.column_store_row_groups: – This view will provide information about how many row groups are inside of column store index and number of rows inside each row group. Also, it would specify persistent state of the row groups. This view 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.

The updateable columnstore first inserts new data into an OPEN rowgroup, which is in rowstore format, and is also sometimes referred to as a delta table. Once an open rowgroup is full, its state changes to CLOSED. A closed rowgroup is compressed into columnstore format by the tuple mover and the state changes to COMPRESSED. The tuple mover is a background process that periodically wakes up and checks whether there are any closed rowgroups that are ready to compress into a columnstore rowgroup. The tuple mover also deallocates any rowgroups in which every row has been deleted. Deallocated rowgroups are marked as TOMBSTONE. To run tuple mover immediately, use the REORGANIZE option of the ALTER INDEX statement.

2.       sys.column_store_dictionaries: –  This is an object catalog view, which records information of dictionary used in segments. Dictionaries are generally used for compressing large datatypes to give a better performance. Dictionaries are used to encode some, but not all data types, therefore not all columns in a column store index have dictionaries. A dictionary can exist as a primary dictionary (for all segments) and possibly for other secondary dictionaries used for a subset of the column's segments.

3.       sys.column_store_segments: This object catalog view stores information related to each row group and columns. For example, a table with 1 row groups and 3 columns returns 3 rows.

Example:

In this example, I would like to see the persistent state of a row group, total rows and deleted rows. Based on the difference between the total rows and deleted rows we can calculate the fullness of the segments. A segment in general can store up to 1 million rows.

Here is an example, of how to create a column store Index. Creating Column Clustered Index

SELECT object_name(i.object_id) AS TableName,

                i.NAME AS IndexName,

                i.index_id,

                i.type_desc,

                CSR.state_description,

                CSR.total_rows,

                CSR.deleted_rows,

                100 * (total_rows - ISNULL(deleted_rows, 0)) / total_rows AS PercentFull

FROM sys.indexes AS i

INNER JOIN sys.column_store_row_groups AS CSR

                ON i.object_id = CSR.object_id

                                AND i.index_id = CSR.index_id

WHERE object_name(i.object_id) = 'Address'

OUTPUT:

TableName	IndexName	index_id	type_desc	state_description	total_rows	deleted_rows	PercentFull
Address	IX_CS_Address	4	NONCLUSTERED COLUMNSTORE	COMPRESSED	515331	0	100