Why Are Many Open Delta Rowgroups Created On A SQL Server Columnstore Table When Simultaneously Trickle Feeding Rows And Running Select Queries?

Question

SQL Server 2022 Express (16.0.1000.6) on Windows Server 2016 Standard.

I have a table with two indexes.

1. An ordered columnstore index (ordered by a smalldatetime column, and then an int column).
2. A unique index to enforce a primary key (on the same two columns as the columnstore index).

The table also has two foreign key constraints that reference the columns in the columnstore index / unique index.

There is a batch job that trickle inserts approximately 3,000 rows each hour, over a period of two minutes, these records are inserted in the same order of the columnstore index. Each row is inserted in a single insert statement.

If no select queries are run during the trickle insert, then a single delta rowgroup is inserted into then eventually closed.

If select queries are run during the trickle insert, then many new open delta rowgroups are created, each with very few rows (1, 3, 3, 2, 2, 2, 3, 8, etc.). This can be seen by selecting from sys.dm_db_column_store_row_group_physical_stats.

I have been unable to determine the reason this is occurring, and have found no articles that describe similar behavior.

Would could be the cause of these large number of open delta rowgroups being created?

Thanks.

Answer 1

There is a batch job that trickle inserts approximately 3,000 rows each hour, over a period of two minutes, these records are inserted in the same order of the columnstore index. Each row is inserted in a single insert statement.

I would change the batch job to do a single insert into the columnstore. There's really no benefit in inserting a single row at a time from an hourly batch job. Doing a larger insert may resolve your delta store issue and you won't need to engage in a time-consuming investigation about the internals of columnstore locking.

With that said, if you have big ambitions, this answer describes a roughly similar scenario to what you're seeing: Why would a table with a Clustered Columnstore Index have many open rowgroups?

Answer 2

The solution I arrived at was to use WITH (TABLOCKX) in the trickle insert statement. It is yet to be seen if there are any longer term implications of doing this but it has solved the problem at hand.

• David Browne - Microsoft: Do you have READ COMMITTED SNAPSHOT enabled for the database? With that the SELECT queries wouldn't need to take S locks on the table, which might also avoid this issue.
• Erik Reasonable Rates Darling: Be careful with using that with column store indexes. If you get a parallel insert plan and not enough rows end up on each thread, you could miss the compression threshold: How TABLOCK Hints Can Hurt Column Store Insert Performance
• Roj: @DavidBrowne, READ COMMITTED SNAPSHOT is not enabled, when I get a chance I will perform some further testing with it enabled.