[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 

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!

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:

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!