I imported data in an SQL Server 2022 database into an Ordered (Timestamp column) Clustered ColumnStoreIndex table in batches of 1,048,576 items. After the import was completed I ran the following query
tables.name AS table_name,
indexes.name AS index_name,
columns.name AS column_name,
INNER JOIN sys.partitions
ON column_store_segments.hobt_id = partitions.hobt_id
INNER JOIN sys.indexes
ON indexes.index_id = partitions.index_id
AND indexes.object_id = partitions.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
AND column_store_segments.column_id = columns.column_id
WHERE tables.name = 'MyTable'
AND columns.name = 'Timestamp'
ORDER BY tables.name, columns.name, col
and I saw that there was not good segment elimination. So I ran
ALTER INDEX CCI_MyTable ON dbo.MyTable REBUILD;
after it was finished I ran the first query again and I noticed even worse results. Before index rebuild I had 19 segments each one having 1,048,576 now I have 30 segments with numbers ranging from 40,000 rows up to 1,048,576. Segment elimination also is much much worse now.
What am I doing wrong?