Why is it considered best practice to partition columnstore tables?
2
votes
1
answer
501
views
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](https://web.archive.org/web/20160603180253/http://www.nikoport.com:80/2015/09/22/columnstore-indexes-part-67-clustered-columstore-isolation-levels-transactional-locking). Why, then, is it still considered best practice to partition them?
I am aware of [the trick](https://web.archive.org/web/20170224141946/http://www.nikoport.com/2014/12/02/clustered-columnstore-indexes-part-45-multi-dimensional-clustering/) 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/ordered-columnstore-indexes?view=sql-server-ver16) .
Asked by J. Mini
(1225 rep)
Jan 20, 2025, 07:29 AM
Last activity: Jan 20, 2025, 01:30 PM
Last activity: Jan 20, 2025, 01:30 PM