IndexOptimize = not working?

[cfasect]

I'm stumped as to why the IndexOptimize is running "successfully" but not actually updating the index. Looking for help from the community.

Is it because of the mirroring?
If so, how can I work around that?

The index is 99% fragmented over 1200+ pages and has over 277,000 rows.

The command I'm running is very specific and targeted as this single table and index.

EXECUTE dbo.IndexOptimize
@Databases = 'mobiledoc_prod',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 15,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@Indexes = 'mobiledoc_prod.dbo.ecwaccesslog%'

The output from the IndexOptimize makes it look like things ran just fine.

Date and time: 2022-03-29 11:45:45
Server: THESQLSERVER
Version: 14.0.3370.1
Edition: Standard Edition (64-bit)
Platform: Windows
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @Databases = 'mobiledoc_prod', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 15, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = 'mobiledoc_prod.dbo.ecwaccesslog102021.idx_userid_mdttime', @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL, @LogToTable = 'N', @Execute = 'Y'
Version: 2020-12-31 18:58:56
Source: https://ola.hallengren.com
	
Date and time: 2022-03-29 11:45:45
Database: [mobiledoc_prod]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Is accessible: Yes
Database mirroring role: PRINCIPAL
	
Date and time: 2022-03-29 11:46:30
	

Completion time: 2022-03-29T11:46:30.3262795-04:00

[stanz71]

I noticed this is an old post, but I am trying to figure out this as well. Did you ever figure out why it didn't seem to be working?

[Rajchau]

I have seen issue with IndexOptimize where if a table has column store index, it skips all indexes on that table. We fixed it in our local copy.
Check if the tables being skipped have a Column Store index on it.

[JDz882]

Rebuild-Online is not a feature available in SQL Standard. Only Enterprise (Maybe Developer as well?)
Try the parameter as this instead for SQL Standard:
@FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',

[Rajchau]

I have seen issue with IndexOptimize where if a table has column store index, it skips all indexes on that table. We fixed it in our local copy.
Check if the tables being skipped have a Column Store index on it.

[iuridosanjos]

Any news on this? I'm trying to use the most simple way and it's keeping a LOT of indexes with high fragmentation. Most of the indexes are type 2 (Nonclustered), few are 1 (clustered), but none Column Store.
I'm not figuring out any particularity about the indexes. I got over 300 indexes that are not getting fixed running IndexOptmize on the simples way possible.
Is it considered a bug or am I doing something wrong?

[josejulioduarte]

Error when execute [master].[dbo].[IndexOptimize] witha parameter SYSTEM_DATABASES an instance in Azure SQL Managed Instance

Ola Hallengren
First of all, I would like to thank you for the excellent work you have done and I am a frequent user of it and I got the error described below in an instance in Azure SQL Managed Instance.
When:

EXECUTE [master].[dbo].[IndexOptimize]
@databases = 'SYSTEM_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = 'Y',
@UpdateStatistics = 'ALL',
@indexes = 'ALL_INDEXES',
@Timelimit = 3600

Give this error:
Msg 50000, Level 16, State 1, Procedure master.dbo.IndexOptimize, Line 1912 [Batch Start Line 2]
Msg 2573, Could not find table or object ID 1287675635. Check system catalog.
Msg 50000, Level 16, State 1, Procedure master.dbo.IndexOptimize, Line 1912 [Batch Start Line 2]
Msg 2573, Could not find table or object ID 1351675863. Check system catalog.
Msg 50000, Level 16, State 1, Procedure master.dbo.IndexOptimize, Line 1912 [Batch Start Line 2]
Msg 2573, Could not find table or object ID 1319675749. Check system catalog.
Msg 50000, Level 16, State 1, Procedure master.dbo.IndexOptimize, Line 1912 [Batch Start Line 2]
Msg 2573, Could not find table or object ID 1255675521. Check system catalog.

When object_id exist in database master

USE [master]
GO
SELECT name FROM sys.all_objects
WHERE object_id IN (1287675635,1351675863,1319675749,1255675521);

Result:
object_name             object_id
SqlServerVersions        1255675521
CommandLog            1287675635
QueueDatabase          1319675749
Queue                  1351675863

Best Regards
José Júlio Duarte

Version: 12.0.2000.8
Edition: SQL Azure
Platform: Windows
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @databases = 'SYSTEM_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @indexes = 'ALL_INDEXES', @Timelimit = 3600, @delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL, @LogToTable = 'Y', @execute = 'Y'
Version: 2024-12-14 16:29:02
Source: https://ola.hallengren.com

Date and time: 2024-12-17 00:42:25
Database: [master]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Is accessible: Yes

Msg 50000, Level 16, State 1, Procedure master.dbo.IndexOptimize, Line 1912 [Batch Start Line 1]
Msg 2573, Could not find table or object ID 1287675635. Check system catalog.

Date and time: 2024-12-17 00:42:25
Database context: [master]
Command: UPDATE STATISTICS [dbo].[CommandLog] [_WA_Sys_00000002_4CC05EF3]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 3376, ModificationCounter: 1
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2024-12-17 00:42:25

Date and time: 2024-12-17 00:42:25
Database context: [master]
Command: UPDATE STATISTICS [dbo].[CommandLog] [_WA_Sys_0000000F_4CC05EF3]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 3377, ModificationCounter: 3
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2024-12-17 00:42:25

Msg 50000, Level 16, State 1, Procedure master.dbo.IndexOptimize, Line 1912 [Batch Start Line 1]
Msg 2573, Could not find table or object ID 1351675863. Check system catalog.

Msg 50000, Level 16, State 1, Procedure master.dbo.IndexOptimize, Line 1912 [Batch Start Line 1]
Msg 2573, Could not find table or object ID 1319675749. Check system catalog.

Msg 50000, Level 16, State 1, Procedure master.dbo.IndexOptimize, Line 1912 [Batch Start Line 1]
Msg 2573, Could not find table or object ID 1255675521. Check system catalog.

Date and time: 2024-12-17 00:42:25
Database: [model]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Is accessible: Yes

Date and time: 2024-12-17 00:42:25
Database: [msdb]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Is accessible: Yes

Date and time: 2024-12-17 00:42:25