Sample Header Ad - 728x90

Unexpected LOB_COMPACTION on DATETIME Column with Ola Hallengren's IndexOptimize

0 votes
1 answer
144 views
I'm using Ola Hallengren's IndexOptimize script for index maintenance on my SQL Server databases. We have a Clustered and 3 non-clustered indexes on a big table having rows over 685 million. Reorganize was running more than 7 hours till morning on a non-clustered index with option LOB_COMPACTION = ON until it's killed explicitly. This index has only one key column on DATETIME type and no covering columns in there. There is one column with type geography on this table but this column is not part of that index. According to my understanding, LOB_COMPACTION should not be applicable to the index since DATETIME is not a LOB data type. I'm expecting when REORGANIZE run on this non-clurtered index, it should run without LOB_COMPACTION = ON. Here is the script I'm running in my SQL Agent job:
EXECUTE dbo.IndexOptimize @Databases = 'Database1,Database2',
                          @LogToTable = 'Y',
                          @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
                          @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
                          @FragmentationLevel1 = 15,
                          @FragmentationLevel2 = 50,
                          @FillFactor = 90,
                          @Indexes = 'ALL_INDEXES',
                          @SortInTempdb = 'Y',
                          @MaxNumberOfPages = 10000000,
                          @Execute = 'Y';
We did not specify the @LOBCompaction parameter, so it defaults to 'Y'. Has anyone else encountered this issue? Is there a known problem with the script, or am I missing something in my configuration? Any insights or suggestions would be greatly appreciated! Thanks!
Asked by Vikas Kumar (11 rep)
Aug 30, 2024, 12:58 PM
Last activity: Aug 4, 2025, 02:01 PM