How to Audit SQL Server Columnstore Index Physical Stats and Fragmentation

Summary: Use sys.dm_db_column_store_row_group_physical_stats to audit the health of your Columnstore indexes, monitor delta store growth, and calculate row-group fragmentation.

Analyzing Columnstore Index Physical Health

Ever wondered how to peek behind the curtains of your SQL Server's Columnstore indexes? Unlike traditional B-Tree indexes, Columnstore indexes are composed of row groups that can be in various states (Open, Closed, or Compressed).

This handy query gives you a detailed look at their physical stats, helping you understand their internal health and identify when it's time for an index reorganize or rebuild.

Columnstore Row Group Physical Stats Query

-- Audit Columnstore row group health and fragmentation
SELECT 
    OBJECT_NAME(ps.object_id) AS [TableName],
    i.[name] AS [IndexName],
    ps.index_id,
    ps.partition_number,
    ps.delta_store_hobt_id,
    ps.state_desc,
    ps.total_rows,
    ps.size_in_bytes,
    ps.trim_reason_desc,
    ps.generation,
    ps.transition_to_compressed_state_desc,
    ps.has_vertipaq_optimization,
    ps.deleted_rows,
    CAST(100.0 * (ISNULL(ps.deleted_rows, 0)) / NULLIF(ps.total_rows, 0) AS DECIMAL(5,2)) AS [Fragmentation %]
FROM sys.dm_db_column_store_row_group_physical_stats AS ps WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) 
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
ORDER BY [TableName], ps.partition_number, ps.row_group_id
OPTION (RECOMPILE);
        

Key Metrics to Monitor

• state_desc: Look for OPEN row groups. If you have too many small open row groups, it can indicate "trickle inserts" which hurt compression and performance.
• Fragmentation %: In Columnstore, fragmentation is caused by deleted_rows. Since rows aren't physically removed from compressed segments immediately, high fragmentation means you are reading "dead" data into memory.
• trim_reason_desc: This tells you why a row group was closed (e.g., BULKLOAD or MEMORY_LIMITATION). If you see MEMORY_LIMITATION frequently, your server may need more RAM to build optimal segments.

This SQL script dives into sys.dm_db_column_store_row_group_physical_stats to provide deep insights. It's an essential tool for any DBA managing large-scale data warehouses or real-time operational analytics.

---

Is your Fragmentation above 20%? Run ALTER INDEX ... REORGANIZE to physically remove deleted rows and merge smaller row groups for better compression!