2

Prior to SQL Server 2016, partitioning columnstore indexes was considered pretty much mandatory because the locks taken on them during inserts/updates/deletes were extreme. However, as of SQL Server 2016, the locks taken on columnstore indexes are much less intense. Why, then, is it still considered best practice to partition them?

I am aware of the trick where you can improve alignment by switching out a partition, building a clustered rowstore index, replacing that index with columnstore, and then switching the partition back in. However, that's just a mild performance optimisation that rarely survives a rebuild and was largely made redundant by SQL Server 2022's ordering features.

Improve this question

1 Answer 1

Reset to default
7

Columnstores are already 'partitioned' by rowgroup.

Partitioning can give you an extra level of elimination beyond the implicit rowgroup partitioning. This is more important than usual because columnstores don't directly support seeks.

It gives you a delta store per partition, rather than one for all rows in the table. If there are significant updates, this can be helpful in retaining elimination benefits. You do need to keep up with maintenance to ensure the number of rows in delta stores doesn't get out of hand (because there are more of them).

Index maintenance can be performed per partition.

You can choose archive compression for less frequently used partitions.

If your proposed usage wouldn't benefit from partitioning, don't use it.

Related Q & A: How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?

Documentation: Columnstore indexes - Design guidance

Finally, don't get too excited about 'ordered columnstore'.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.