This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced sys.dm_db_column_store_row_group_physical_stats system view for finding the information on Columnstore row groups and related fragmentation.
This is an extended version of sys.column_store_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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT OBJECT_NAME([i].[object_id]) AS TableName ,[i].[name] AS IndexName ,[csrgps].[state_desc] ,[csrgps].[total_rows] ,[csrgps].[deleted_rows] ,[csrgps].[size_in_bytes] ,[csrgps].[trim_reason_desc] ,[csrgps].[transition_to_compressed_state_desc] ,[csrgps].[has_vertipaq_optimization] ,[csrgps].[created_time] ,[csrgps].[closed_time] ,100 * (ISNULL(csrgps.[deleted_rows],0)) / total_rows AS 'IsFragmentation' FROM sys.indexes AS i INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS csrgps ON [i].[object_id] = [csrgps].[object_id] AND [i].index_id = [csrgps].index_id ORDER BY [TableName], [IndexName] GO |
Leave a Reply