Fragmentation in Columnstore Indexes: Find and Fix It

Fragmentation in columnstore indexes is not the page fragmentation of a normal index. It means two things. Deleted rows stay inside compressed row groups, and row groups end up smaller than they should be. A view shows both, and two commands fix them.

What Fragmentation Means Here

Fragmentation in columnstore indexes has its own meaning. A columnstore index stores rows in row groups of up to 1,048,576 rows. Each row group is compressed as a unit. When you delete a row, SQL Server does not rewrite the row group. It marks the row in a delete bitmap and skips it from then on. The row still takes space, and every scan still reads past it.

So the useful measure is the share of deleted rows in each row group. A second measure is size. A row group with far fewer rows than the maximum wastes part of the compression. Compression works best on large groups.

Build the Demo and Read the Row Groups

The demo database is named ColumnstoreFragDemo, so run the script on a test server. It builds a table with a clustered columnstore index and loads 2,200,000 rows. GENERATE_SERIES needs SQL Server 2022 or later and a database at compatibility level 160. The script then creates a view over the row group statistics and reads it. On the test server, every read of that statistics view prints a warning that the join order has been enforced. The warning is harmless.

IF DB_ID(N'ColumnstoreFragDemo') IS NULL CREATE DATABASE ColumnstoreFragDemo;
GO
USE ColumnstoreFragDemo;
GO
DROP TABLE IF EXISTS dbo.Readings;
CREATE TABLE dbo.Readings (ReadingID int NOT NULL, SensorCode int NOT NULL, Reading decimal(9,2) NOT NULL, INDEX CCI_Readings CLUSTERED COLUMNSTORE);
GO
INSERT dbo.Readings WITH (TABLOCK) (ReadingID, SensorCode, Reading)
SELECT value, value % 100, 1.5 FROM GENERATE_SERIES(1, 2200000) OPTION (MAXDOP 1);
GO
CREATE OR ALTER VIEW dbo.RowgroupHealth AS
SELECT rg.row_group_id AS RowgroupID, rg.state_desc AS State, rg.total_rows AS TotalRows, rg.deleted_rows AS DeletedRows,
       CAST(rg.deleted_rows * 100.0 / NULLIF(rg.total_rows, 0) AS decimal(5,1)) AS DeletedPercent
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
WHERE rg.object_id = OBJECT_ID(N'dbo.Readings');
GO
SELECT * FROM dbo.RowgroupHealth ORDER BY RowgroupID;

The load made three row groups. Two are full. The third holds only 102,848 rows, because the load ended there. Nothing is deleted yet.

The INSERT has OPTION (MAXDOP 1) on purpose. A parallel load splits the rows across its threads, and each thread builds smaller row groups. A second server with 20 CPUs loaded the same rows in parallel and made ten row groups of 220,000 rows. Now delete every fourth row and read the view again.

DELETE dbo.Readings WHERE ReadingID % 4 = 0;
SELECT * FROM dbo.RowgroupHealth ORDER BY RowgroupID;

Each row group now carries 25 percent deleted rows. The TotalRows column did not change, because the rows are still there. Only the delete bitmap moved. A scan reads all 2,200,000 rows and throws a quarter of them away.

One Number Per Index

The per row group view is the detail. For a daily check of fragmentation in columnstore indexes, one row per index is easier to read. The next query sums the rows of the compressed row groups and divides the deleted ones by the total.

SELECT OBJECT_NAME(rg.object_id) AS TableName, i.name AS IndexName,
       SUM(rg.total_rows) AS TotalRows, SUM(rg.deleted_rows) AS DeletedRows,
       CAST(SUM(rg.deleted_rows) * 100.0 / NULLIF(SUM(rg.total_rows), 0) AS decimal(5,1)) AS DeletedPercent
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
JOIN sys.indexes AS i ON i.object_id = rg.object_id AND i.index_id = rg.index_id
WHERE rg.state_desc = N'COMPRESSED'
GROUP BY rg.object_id, i.name;

The index holds 25.0 percent deleted rows. I start to look at an index when the figure passes 20 percent. That is a rule of thumb, not a limit, so tune it for your tables.

Fix It With REORGANIZE

REORGANIZE works online. It rewrites the row groups that hold deleted rows and merges small ones. Nobody is blocked, and it needs little extra space. This is the right choice for a large table with no room for a full rebuild. The next script runs it and reads the row groups.

ALTER INDEX CCI_Readings ON dbo.Readings REORGANIZE;
SELECT * FROM dbo.RowgroupHealth ORDER BY RowgroupID;

The three old row groups are tombstones, which SQL Server removes later in the background. Two new compressed row groups took their place. They hold 863,568 and 786,432 rows, together the 1,650,000 live rows. No deleted rows remain, and the number of row groups went down from three to two.

Fix It With REBUILD

REBUILD recreates the whole index. It takes more time, CPU and space than REORGANIZE, and it resets everything. Use it when most of the table changed, or when the row groups are small for other reasons. The next script rebuilds the index and reads the row groups once more.

ALTER INDEX CCI_Readings ON dbo.Readings REBUILD;
SELECT * FROM dbo.RowgroupHealth ORDER BY RowgroupID;

The rebuild also removed every deleted row. It produced one full row group and two smaller ones. The test server has a MAXDOP of 2, and a parallel rebuild splits the rows between its threads. The split can differ on your server. So a rebuild is not always the best way to get large row groups. In this run, the reorganize left two row groups and the rebuild left three.

You could argue that a rebuild is simpler, because it gives a clean index and one command. That is true for a small table. For a large one, it needs a maintenance window and extra space. REORGANIZE gets most of the benefit while users keep working.

What to Remember

Measure fragmentation in columnstore indexes as the share of deleted rows in each row group. Watch for row groups far below the maximum size. Use REORGANIZE first on a busy or large table. Use REBUILD when most of the table changed. Read the row group view again after each command.

When you finish testing, remove the example database.

USE master;
GO
IF DB_ID(N'ColumnstoreFragDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ColumnstoreFragDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ColumnstoreFragDemo;
END;

Fragmentation is not wasted space you can see, it is deleted rows you still read.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.