0

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.

2 Answers 2

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?

1
  • Joe, thanks for the response, it is much appreciated, I will take all of this into consideration.
    – Roj
    Sep 24, 2023 at 13:06
0

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.

3
  • 2
    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. Dec 3, 2023 at 20:09
  • 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 Dec 3, 2023 at 20:50
  • @DavidBrowne, READ COMMITTED SNAPSHOT is not enabled, when I get a chance I will perform some further testing with it enabled.
    – Roj
    Dec 3, 2023 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.