SQL Server 2014: sys.column_store_row_groups to find information on Columnstore row groups

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Server 2014 introduced sys.column_store_row_groups system view for finding the information on the Columnstore row groups.

Using this system view, you can find information like total number of rows physically stored, and a column for the number of rows marked as deleted.

If you find a high volume of deleted rows, you should decide to rebuild it.

T-SQL Script:

SELECT

OBJECT_NAME([csrg].[object_id]) AS TableName

,[i].[name] AS IndexName

,[csrg].[row_group_id]

,[csrg].[delta_store_hobt_id]

,[csrg].[state_description]

,[csrg].[total_rows]

,[csrg].[deleted_rows]

,[csrg].[size_in_bytes]

FROM sys.column_store_row_groups AS csrg

LEFT OUTER JOIN sys.indexes AS i

ON [csrg].[object_id] = [i].[object_id]

AND [csrg].[index_id] = [i].[index_id]

ORDER BY [TableName],[IndexName]

Jan 10, 2018Anvesh Patel

Database Engineer