IndexOptimize - wrong ONLINE setting when table contains ColumnStore index

[ExigoAaronLopez]

Description of the issue
The IndexOptimize procedure sets ONLINE option to OFF for every index in a table that contains at least, one ColumnStore index instead of appliying it for the ColumnStore ones only.

SQL Server version and edition
Microsoft SQL Azure (RTM) - 12.0.2000.8 May 11 2024 17:25:03 Copyright (C) 2022 Microsoft Corporation

Version of the script
Version: 2020-12-06 00:32:13

What command are you executing?
DECLARE @dbname NVARCHAR(255)
, @indexes NVARCHAR(512) /Set the start time as the current local time/

SET @dbname = DB_NAME(); /Set the list of indexes to maintain. All except those under bak schema/
SET @indexes = '' + @dbname + '.dbo.%';
EXECUTE dbo.IndexOptimize @databases = @dbname
, @indexes = @indexes
, @FragmentationLow = NULL
, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
, @FragmentationLevel1 = 10
, @FragmentationLevel2 = 30
, @MinNumberOfPages = 1
, @SortInTempdb = 'Y'
, @WaitAtLowPriorityMaxDuration = 1
, @WaitAtLowPriorityAbortAfterWait = 'SELF'
, @LockMessageSeverity = 10
, @LogToTable = 'Y'

What output are you getting?
All indexes in tables that contains at least one ColumnStore index, get processed in OFFLINE mode, which increases the locking overall

[pj-diller]

I hadn't noticed this behavior about ALL indexes when there's a clustered columnstore index, but I do wonder about the choice NOT to perform clustered columnstore index rebuilds online when using an appropriate edition, generally speaking.

I'm not sure of the history here, but when I put together and run an ONLINE rebuild of a clustered columnstore index today in an Azure SQL Database, it seems to be working just fine. However, the script will disable that opportunity and always run OFFLINE. :)

I wonder if there is anyone who knows whether this is a hold-over from a time when the ONLINE option wasn't possible, or if there's something else happening that I'm missing when I apparently "successfully" rebuild a CCI online?

[AvdbergDB]

I discovered this behaviour today.
I have tables with a clustered rowstore-index and several nonclustered indexes, and also one nonclustered columnstore index.
Index-rebuilds on the nonclustered rowstore-indexes on those tables are done offline instead of online after creating the columnstore index.
The columnstore index itself is skipped completely.

I just updated the maintenancesolution to make sure that is not the cause of the issues (Version: 2025-01-18 21:00:27)

SQL version: Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5029187) - 13.0.7029.3 (X64) Aug 16 2023 19:44:44 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

One example is this:

Index IX1 is a regular nonclustered rowstore index, it does not contain blobs and is NOT compressed.
The table contains a nonclustered columnstore index.
The index specified is not a columnstore index, although the findings of indexoptimize indicate it as such: 'Columnstore: Yes' (see below)

Date and time: 2025-01-22 09:46:55
Database context: [db]
Command: ALTER INDEX [IX1] ON [dbo].[Table] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: Yes, AllowPageLocks: Yes, PageCount: 22377, Fragmentation: 73.5621
Outcome: Succeeded
Duration: 00:00:12
Date and time: 2025-01-22 09:47:07

But why the changed behaviour for the rowstore-indexes?