Reducing Fragmentation on a clustered columstore index

Question

I have a database with a clustered columnstore index on it. The table has 1,685,123,846 records in it.

I use the ola hallengre script monthly to rebuild the index, but it takes so long to run before being killed after 8 hours I wasn't convinced it was actually processing most of the indexes.

I manually recreated the index using WITH (DROP_EXISTING = ON);. This took 10 hours.

After running this I decided to check the fragmentation using this command:

select database_id, object_id, index_id, partition_number, index_type_desc, index_depth, index_level,avg_fragmentation_in_percent, fragment_count, page_count  from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('ConsumerReading'), null, null, null) 

Its showing me the following data:

database_id	object_id	index_id	partition_number	index_type_desc	index_depth	index_level	avg_fragmentation_in_percent	fragment_count	page_count
5	626101271	1	1	CLUSTERED INDEX	2	0	97.59036144578313	166	166
5	626101271	1	1	CLUSTERED INDEX	3	0	0.9500950095009502	1258	9999
5	626101271	2	1	NONCLUSTERED INDEX	5	0	2.9880120735355384	815946	9788682
5	626101271	3	1	NONCLUSTERED INDEX	4	0	0.17827134824641383	808018	14853761
5	626101271	4	1	NONCLUSTERED INDEX	4	0	6.488527005131855	546542	3983354

Index id 1 is the index I recreated so I think thats as defragmented as it can get. However it's showing a fragmentation of 97%. However, that index is in twice... and the second time it's only 0.95%.

The issue I have is if I rebuild the index, then run the ola-hallengre script on it straight away, it thinks it needs to be rebuild again, and unless I'm missing something as it clearly shouldn't need to be.

My question is why is that index in there twice? Is there a trick I'm missing with the ola-hallengre script and columnstore index? I'm ruinning this in Azure so I don't think partitioning the columnstore index makes sense but I could be wrong.

Any advice would be appreciated!

[edit]

The table has the following indexes on it:

CREATE CLUSTERED COLUMNSTORE INDEX IX_CONSUMER
ON ConsumerReading;

GO

CREATE UNIQUE NONCLUSTERED INDEX IX_CONSUMER_PRIMARYKEY ON [dbo].ConsumerReading
(
    [SensorReading] ASC,
    [VesselId] ASC,
    [Name] ASC
)

GO

CREATE NONCLUSTERED INDEX [IX_Consumer_ConsumerTypes] ON [dbo].[ConsumerReading]
(
    [SensorReading] ASC,
    [VesselId] ASC
)
INCLUDE (   [Name],
    [ConsumerType]) 
WHERE ([ConsumingFuel]=(1))
WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ConsumerReading_ByVesselAndReadingDate] ON [dbo].[ConsumerReading]
(
    [VesselId] ASC,
    [SensorReading] ASC
)
INCLUDE (   [Name],
    [ConsumerType],
    [ReturnVolumeFlow],
    [SupplyVolumeFlow],
    [SupplyDensity]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Answer 1

First of all you should ask yourself why I am doing it?

Correct answer should be something like "because I measured queries of my beloved users and found performance gain of X ms of response time, Y lio, Z mc of CPU time multiply by N queries and it overweight the price of 10 hours of my time, plus extra log growth, plus extra workload for HA/DR/replication to rebuild the indexes".

Why index rebuild of clustered columnstore indexes could be benefitial

1. delete/update

Deletes hurts performance twice - not only SQL server have to read unnecessary data but also it have to read delete bitmap to adjust results read on previous step

Updates or 'soft' deletes (with updating isDeleted flag) even worse because in addition to delete bitmap there is an insert into delta store.

Fortunately major use case for clustered columnstore indexes is fact tables and they are tend to be 'insert only'.

2. size of delta store and number of rowgroups

Next problem could be solved automatically via process called 'tuple mover' but it's not as efficient as if you rebuild the index.

3. rowgroup elimination

Every rowgroup has min and max of every column so rowgroup could be skipped. It works the best with date filters for fact tables like query 'give me sales for the last month' could skip rowgroups where sales date older than last month.

However it's not easy to achieve it, sql server tends to mix rows up. Fortunately in SQL 2022 there is an option of ordered columnstore https://learn.microsoft.com/en-us/sql/t-sql/statements/create-columnstore-index-transact-sql?view=sql-server-ver16#order

However there are some question to this https://www.brentozar.com/archive/2022/07/columnstore-indexes-are-finally-sorted-in-sql-server-2022/

How to make the process less painful?

The answer is - partitioning. It works the best with partitioning by date so you can tune older static partitions and do not touch it anymore. Index rebuild happened only for current active partitions.

Additional bonus - you can put static partitions to read-only filegroup and exclude it from backup/recovery. It makes the process more complex but faster.