0

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
7
  • Is it a columnstore index or a regular row-store index? Your title say columnstore, but the ouput from sys.dm_db_index_physical_stats looks to me like a rowstore index. Did you run that query while the defrag was running? Perhaps you see some intermediate index, possibly a artifact from using ONLINE? Just thinking out loud here... Nov 12, 2023 at 13:52
  • 1
    Why in the name of Big Baby Jesus are you worried about logical fragmentation? Nov 12, 2023 at 16:11
  • @ErikDarling My concern is only that if I run the ola hallengre maintence script twice in a row it will rebuild the clustered index again even though it clearly must not need it. It also shows that the index is heavily fragmented. I'm trying to figure out why. Nov 12, 2023 at 16:14
  • 2
    Worrying about logical fragmentation, especially in a columnstore index, is like worrying about potholes in the road when you’re on an airplane. Stop believing in memes. Nov 12, 2023 at 17:14
  • 2
    As Erik said, fragmentation is irrelevant, particularly for columnstore indexes. There are other aspects regarding columnstore indexes that you can consider, but that is rather the index degrading over time, not fragmentation. read up on "rowgroup elimination" as a starter to see if this is relevant for you. You will handle your columnstore indexes separately from your row-indexes, regarding the decision whether to do rebuild or reorganize. Nov 12, 2023 at 18:27

1 Answer 1

0

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'.

  1. 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.

  1. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.