Index Maintenance Miscalculating Page Numbers

[SQL-Simon]

The problem is that for indexes that have a large proportion of LOB_DATA pages, it effectively ignores @MaxNumberOfPages.
When @CurrentCommand is set in sproc dbo.IndexOptimize, it runs a query against sys.dm_db_index_physical_stats to get the number of pages used (amongst other things) for the current index. But it restricts alloc_unit_type_desc to "IN_ROW_DATA", i.e. it excludes LOB data. But when it rebuilds an index, it uses the default of LOB_COMPACTION = ON, and so rebuilds the entire index. So if you have an index with say 1,167,540 IN_ROW_DATA pages (~9GB), but 40,318,700 LOB_DATA pages (~307GB), it will rebuild the index with LOB_COMPACTION = ON, even if you 've set @MaxNumberOfPages = 2097152 (16GB).
So you end up trying to rebuild a 300+GB index, even though you specified a max of 16GB, and can run out of space.
If it reorganises the index, it uses @LOBCompaction to determine whether to compact the LOB data, but not for rebuilds.

So we need an option to say whether we want the @MaxNumberOfPages to look at just IN_ROW_DATA or LOB_DATA pages as well. Or better still, an extra @MaxNumberOfLOBPages parameter where we can specify the max number of LOB pages, and it will then only rebuild\reorganise the index if the neither of the parameters is exceeded.

SQL Server version and edition
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

Version of the script
Version: 2020-01-26 14:06:53

What command are you executing?
EXECUTE @returncode =
Admin.dbo.IndexOptimize
@databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 30,
@MinNumberOfPages = 7000,
@MaxNumberOfPages = 2097152,
@SortInTempdb = 'N',
@FillFactor = 100,
@LogToTable = 'Y',
@indexes = 'ALL_INDEXES',
@Timelimit = 7200

What output are you getting?
A disk full error!

Thanks
Simon Dooley