Skip to content

Columnstore index disables online index rebuild for all indexes on a table #534

Closed
@LukasMakovicka

Description

@LukasMakovicka

Description of the issue
When there is a columnstore index on a table all the other (rowstore) indexes on the table are rebuilt offline. This behavior has no reason because the existence of the columnstore index does not prevent SQL Server from doing index rebuild online.

There is only one occasion when the columnstore index blocks online index operation:
When there is a clustered columnstore index then a rowstore index cannot be created online. Though online rowstore index rebuild is still possible. But since index maintenance does not create new indexes this case can be omitted.

I work in an eshop company. Our web must be online 24 / 7. This means on the database level that it's not possible to lock a table with a Sch-M lock for a long time. So all index rebuilds are done online when possible. This bug with columnstore disabling online index rebuild is a serious problem for us because it creates hundreds of blocks in our database.

SQL Server version and edition
Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Jan 25 2021 20:16:12 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )

Version of the script
2020-12-31 18:58:56

What command are you executing?
EXEC dbo.IndexOptimize
@databases = 'USER_DATABASES',
@indexes = 'ALL_INDEXES',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 25,
@FragmentationMedium = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@StatisticsSample = 100,
@WaitAtLowPriorityMaxDuration = 2,
@WaitAtLowPriorityAbortAfterWait = 'BLOCKERS',
@MaxDOP = 12,
@PartitionLevel = 'N',
@SortInTempdb = 'Y',
@LogToTable = 'Y'

What output are you getting?
ALTER INDEX [IX_MyTable_Search] ON [MyDb].[dbo].[MyTable] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF, MAXDOP = 12)

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions