COMPRESSION_DELAY Option for Columnstore Indexes: Test It

The COMPRESSION_DELAY option keeps a closed delta rowgroup uncompressed for a number of minutes. Rows that change soon after they arrive are then cheaper to modify. Rows that stay put are compressed as usual once the delay has passed.

Where New Rows Go

A columnstore index does not compress every row the moment it arrives. Small inserts land in a delta rowgroup, which is a normal B-tree structure. When the delta rowgroup reaches 1,048,576 rows, SQL Server closes it. A background task called the tuple mover then compresses closed rowgroups into the columnstore format.

Compressed rowgroups are not edited in place. A delete only marks the row in a delete bitmap. An update is a delete plus an insert into a delta rowgroup. If rows change soon after they arrive, compressing them at once means that every change leaves a deleted row behind.

What the Option Does

The COMPRESSION_DELAY option is a number of minutes. A closed delta rowgroup stays in the delta store at least that long before the tuple mover can compress it. The default is 0, which means compress as soon as the tuple mover runs. The allowed range is 0 to 10,080 minutes, which is one week. A client had terabyte columnstore tables and changed rows soon after loading them. A delay of 10 minutes made the slowdown go away.

The option helps when new rows change again soon after they arrive. A status can move from new to paid. A row can get corrected in the next hour. It does not help a pure append workload, because nothing changes afterwards.

Build Two Tables

The demo database is named CompressionDelayDemo, so run the script on a test server. It builds two tables with the same columns and a clustered columnstore index each. FreshRows sets a delay of 10 minutes. SettledRows keeps the default. A view reads the rowgroup states, and the last query shows the option in sys.indexes.

IF DB_ID(N'CompressionDelayDemo') IS NULL CREATE DATABASE CompressionDelayDemo;
GO
USE CompressionDelayDemo;
GO
DROP TABLE IF EXISTS dbo.FreshRows, dbo.SettledRows;
CREATE TABLE dbo.FreshRows (RowID int NOT NULL, Payload int NOT NULL, INDEX CCI_FreshRows CLUSTERED COLUMNSTORE WITH (COMPRESSION_DELAY = 10 MINUTES));
CREATE TABLE dbo.SettledRows (RowID int NOT NULL, Payload int NOT NULL, INDEX CCI_SettledRows CLUSTERED COLUMNSTORE);
GO
CREATE OR ALTER VIEW dbo.RowgroupStates AS
SELECT OBJECT_NAME(object_id) AS TableName, row_group_id AS RowgroupID, state_desc AS State, total_rows AS TotalRows, deleted_rows AS DeletedRows
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id IN (OBJECT_ID(N'dbo.FreshRows'), OBJECT_ID(N'dbo.SettledRows'));
GO
SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName, compression_delay FROM sys.indexes WHERE name IN (N'CCI_FreshRows', N'CCI_SettledRows');

Load Rows in Small Batches

A batch of 102,400 rows or more goes straight into a compressed rowgroup, so the demo loads smaller batches. The script runs 22 batches of 50,000 rows into each table. GENERATE_SERIES needs SQL Server 2022 or later. The loop is bounded at 22 passes. Then it reads the rowgroup states.

DECLARE @i int = 0;
WHILE @i < 22
BEGIN
    INSERT dbo.FreshRows (RowID, Payload) SELECT @i * 50000 + value, 1 FROM GENERATE_SERIES(1, 50000);
    INSERT dbo.SettledRows (RowID, Payload) SELECT @i * 50000 + value, 1 FROM GENERATE_SERIES(1, 50000);
    SET @i += 1;
END;
SELECT * FROM dbo.RowgroupStates ORDER BY TableName, RowgroupID;

Both tables look the same. Each has a closed rowgroup of 1,048,576 rows and an open one with the rest. The tuple mover would compress SettledRows at its next run. It has to leave FreshRows alone for 10 minutes. Waiting for the tuple mover takes minutes, so the next step plays its part by hand. Run the next scripts right away. After about five minutes the tuple mover compresses SettledRows by itself, and after ten minutes it compresses FreshRows as well.

Change Rows Before and After Compression

The next script compresses the rowgroups of SettledRows with REORGANIZE and COMPRESS_ALL_ROW_GROUPS. That stands in for a tuple mover that already ran. It also compresses the open rowgroup, which the tuple mover would not. That does not change the update result below. Then it updates the first 20,000 rows in both tables, rows that arrived in the first batches.

ALTER INDEX CCI_SettledRows ON dbo.SettledRows REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
GO
UPDATE dbo.FreshRows SET Payload = 2 WHERE RowID <= 20000;
UPDATE dbo.SettledRows SET Payload = 2 WHERE RowID <= 20000;
GO
SELECT * FROM dbo.RowgroupStates ORDER BY TableName, RowgroupID;

The difference is clear. In FreshRows, the update changed rows inside the delta rowgroup. The closed rowgroup shrank by 20,000 rows, the open one grew by 20,000, and no deleted rows remain. In SettledRows, the compressed rowgroup keeps all its rows and marks 20,000 of them as deleted. A new open rowgroup holds the 20,000 new versions. The deleted rows stay until a reorganize or rebuild removes them. The deleted rows that remain are measured in Fragmentation in Columnstore Indexes: Find and Fix It.

Reading the rowgroup view prints a warning about the join order. It is harmless and does not change the result. The two tombstone rowgroups are the old rowgroups of SettledRows, which the reorganize replaced. Cleanup removes them later.

Set or Change the Option

You can change the COMPRESSION_DELAY option on an existing index with ALTER INDEX and SET. The unit is MINUTES, and the keyword can be written in any case. The script sets the delay to 0 and then back to 10, and reads the value each time.

ALTER INDEX CCI_FreshRows ON dbo.FreshRows SET (COMPRESSION_DELAY = 0);
SELECT name, compression_delay FROM sys.indexes WHERE name = N'CCI_FreshRows';

ALTER INDEX CCI_FreshRows ON dbo.FreshRows SET (COMPRESSION_DELAY = 10 Minutes);
SELECT name, compression_delay FROM sys.indexes WHERE name = N'CCI_FreshRows';

What the Delay Costs

Rows in a delta rowgroup are not compressed. Queries read them row by row, and they take more space than compressed rows. A long delay on a large, busy table keeps many rows in that slower form. Pick a delay that matches how long rows keep changing, and measure the query time before and after.

You could argue that the delay only postpones the problem, because the rows are compressed in the end. That is the point. Rows that change in the first hour are modified cheaply in the delta store. Rows that stop changing are compressed once, with no deleted rows left behind.

What to Remember

The COMPRESSION_DELAY option fits columnstore tables whose new rows change soon after they arrive. It keeps closed delta rowgroups open for a number of minutes. Updates then do not leave deleted rows in compressed rowgroups. Check the deleted rows per rowgroup before and after. Leave the default for append only tables.

When you finish testing, remove the example database.

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

A delay is not a slowdown, it is a chance for a row to change before it is frozen.

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