IndexOptimize.sql reorganize indexes

[gijsdamen]

I use the IndexOptimize.sql tool, Committed on Feb 16, 2025
I notice that tool is not reorganizing my indexes, only the rebuild is accepted as action for the indexes.
I use this statement:

EXECUTE dbo.IndexOptimize
@Databases = ‘xxxxx’
,@FragmentationLow = NULL
,@FragmentationMedium = 'INDEX_REORGANIZE'
,@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE'
,@FragmentationLevel1 = 10
,@FragmentationLevel2 = 30
,@Indexes = 'ALL_INDEXES'
,@LogToTable = 'Y'
,@OnlyModifiedStatistics='Y'

When checking the IndexOptimize.sql code I find that this code selects the accepted action on an index:

         IF NOT (@CurrentOnReadOnlyFileGroup = 1)
          AND NOT (@CurrentIsMemoryOptimized = 1)
          AND NOT (@CurrentAllowPageLocks = 1)
          BEGIN
            INSERT INTO @CurrentActionsAllowed ([Action])
            VALUES ('INDEX_REORGANIZE')
          END
          IF NOT (@CurrentOnReadOnlyFileGroup = 1)
          AND NOT (@CurrentIsMemoryOptimized = 1)
          BEGIN
            INSERT INTO @CurrentActionsAllowed ([Action])
            VALUES ('INDEX_REBUILD_OFFLINE')
          END

This snipped exclude indexes from reorganization when they allow page locks (NOT @CurrentAllowPageLocks = 1).
Why? This is in contradiction to your previous answer #439
ALTER INDEX ... REORGANIZE is not supported in the following scenarios:

1. Page locks is disabled on the index, sys.indexes.allow_page_locks = 0
2. It is a memory optimized table, sys.tables.is_memory_optimized = 1
   See also this blog which explains why indexes can’t be reorganized when the page level locks are disabled: : https://www.mssqltips.com/sqlservertip/4247/resolve-sql-server-database-index-reorganization-page-level-locking-problem/

[olahallengren]

This is a bug. I introduced it in the 9 Feb 2025 version, when I was doing some small refactoring to fix some other issues.

Thank you for catching it. I have fixed it now.

[gijsdamen]

👍