sys.dm_db_column_store_row_group_physical_stats

The DMV called sys.dm_db_column_store_row_group_physical_stats helps with reviewing column store indexes and especially deciding when the deleted rows (fragmentation) value is high.
 
Performance issues with this DMV were resolved in SQL Server 2016 SP1 CU1.
 
Columns returned are:
image1
Below is sample query to include the table and index name:

SELECT 
'[' + OBJECT_SCHEMA_NAME(a.[object_id]) + '].[' + OBJECT_NAME(a.[object_id]) + ']' AS table_name
,b.[name] AS column_store_index_name
,a.* 
FROM sys.dm_db_column_store_row_group_physical_stats a, sys.indexes b
WHERE 1=1
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id;

image2
Further info can be found here.

Leave a Reply