Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
LukasMakovicka opened this issue Jun 14, 2021 · 0 comments · May be fixed by #609
Open

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

LukasMakovicka opened this issue Jun 14, 2021 · 0 comments · May be fixed by #609

Comments

@LukasMakovicka
Copy link

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants