IndexOptimize query taking 7+ hours

[willim32533]

This query that IndexOptimize generates and executes has consistently taken 7+ hours to complete over the past year+. The database has about 20 base tables that are partitioned (approximately 311 partitions) and roughly 7TB in total size. In contrast, the same query executes on two other sql servers in less than few minutes with the identical schema, CPU, and RAM with the only difference being data and number of partitions - approximately 133 and 149 - and size being roughly 8TB and 9TB.

Then, this other IndexOptimize query takes at least 5 to 8 hours to run on each server:

SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), 
@ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(DB_ID(@ParamDatabaseName), 
@ParamObjectID, @ParamIndexID, @ParamPartitionNumber, 'LIMITED') WHERE alloc_unit_type_desc = 
'IN_ROW_DATA' AND index_level = 0

Any ideas?

-- query that takes hours on 1 server, but minutes on 2 other similar servers
SELECT SchemaID, 
   SchemaName, 
   ObjectID, 
   ObjectName, 
   ObjectType, 
   IsMemoryOptimized, 
   IndexID, 
   IndexName, 
   IndexType, 
   AllowPageLocks, 
   IsImageText, 
   IsNewLOB, 
   IsFileStream, 
   IsColumnStore, 
   IsComputed, 
   IsTimestamp, 
   OnReadOnlyFileGroup, 
   ResumableIndexOperation, 
   StatisticsID, 
   StatisticsName, 
   NoRecompute, 
   IsIncremental, 
   PartitionID, 
   PartitionNumber, 
   PartitionCount, [Order]
   , 
   Selected, 
   Completed 
FROM 
   (
   SELECT schemas.[schema_id] AS SchemaID, 
      schemas.[name] AS SchemaName, 
      objects.[object_id] AS ObjectID, 
      objects.[name] AS ObjectName, 
      RTRIM(objects.[type]) AS ObjectType, 
      tables.is_memory_optimized AS IsMemoryOptimized, 
      indexes.index_id AS IndexID, 
      indexes.[name] AS IndexName, 
      indexes.[type] AS IndexType, 
      indexes.allow_page_locks AS AllowPageLocks, 
      CASE 
         WHEN indexes.[type] = 1 
        AND EXISTS
            (
            SELECT * 
            FROM sys.columns columns 
            INNER JOIN sys.types types 
            ON columns.system_type_id = types.user_type_id 
            WHERE columns.[object_id] = objects.object_id 
           AND types.name IN('image','text','ntext')
            ) THEN 1 
         ELSE 0 
      END AS IsImageText, 
      CASE 
         WHEN indexes.[type] = 1 
        AND EXISTS
            (
            SELECT * 
            FROM sys.columns columns 
            INNER JOIN sys.types types 
            ON columns.system_type_id = types.user_type_id 
            OR (columns.user_type_id = types.user_type_id 
           AND types.is_assembly_type = 1) 
            WHERE columns.[object_id] = objects.object_id 
           AND (types.name IN('xml') 
            OR (types.name IN('varchar','nvarchar','varbinary') 
           AND columns.max_length = -1) 
            OR (types.is_assembly_type = 1 
           AND columns.max_length = -1))
            ) THEN 1 
         WHEN indexes.[type] = 2 
        AND EXISTS
            (
            SELECT * 
            FROM sys.index_columns index_columns 
            INNER JOIN sys.columns columns 
            ON index_columns.[object_id] = columns.[object_id] 
           AND index_columns.column_id = columns.column_id 
            INNER JOIN sys.types types 
            ON columns.system_type_id = types.user_type_id 
            OR (columns.user_type_id = types.user_type_id 
           AND types.is_assembly_type = 1) 
            WHERE index_columns.[object_id] = objects.object_id 
           AND index_columns.index_id = indexes.index_id 
           AND (types.[name] IN('xml') 
            OR (types.[name] IN('varchar','nvarchar','varbinary') 
           AND columns.max_length = -1) 
            OR (types.is_assembly_type = 1 
           AND columns.max_length = -1))
            ) THEN 1 
         ELSE 0 
      END AS IsNewLOB, 
      CASE 
         WHEN indexes.[type] = 1 
        AND EXISTS
            (
            SELECT * 
            FROM sys.columns columns 
            WHERE columns.[object_id] = objects.object_id 
           AND columns.is_filestream = 1
            ) THEN 1 
         ELSE 0 
      END AS IsFileStream, 
      CASE 
         WHEN EXISTS
            (
            SELECT * 
            FROM sys.indexes indexes 
            WHERE indexes.[object_id] = objects.object_id 
           AND [type] IN(5,6)
            ) THEN 1 
         ELSE 0 
      END AS IsColumnStore, 
      CASE 
         WHEN EXISTS
            (
            SELECT * 
            FROM sys.index_columns index_columns 
            INNER JOIN sys.columns columns 
            ON index_columns.object_id = columns.object_id 
           AND index_columns.column_id = columns.column_id 
            WHERE (index_columns.key_ordinal > 0 
            OR index_columns.partition_ordinal > 0) 
           AND columns.is_computed = 1 
           AND index_columns.object_id = indexes.object_id 
           AND index_columns.index_id = indexes.index_id
            ) THEN 1 
         ELSE 0 
      END AS IsComputed, 
      CASE 
         WHEN EXISTS
            (
            SELECT * 
            FROM sys.index_columns index_columns 
            INNER JOIN sys.columns columns 
            ON index_columns.[object_id] = columns.[object_id] 
           AND index_columns.column_id = columns.column_id 
            INNER JOIN sys.types types 
            ON columns.system_type_id = types.system_type_id 
            WHERE index_columns.[object_id] = objects.object_id 
           AND index_columns.index_id = indexes.index_id 
           AND types.[name] = 'timestamp'
            ) THEN 1 
         ELSE 0 
      END AS IsTimestamp, 
      CASE 
         WHEN EXISTS 
            (
            SELECT * 
            FROM sys.indexes indexes2 
            INNER JOIN sys.destination_data_spaces destination_data_spaces 
            ON indexes.data_space_id = destination_data_spaces.partition_scheme_id 
            INNER JOIN sys.filegroups filegroups 
            ON destination_data_spaces.data_space_id = filegroups.data_space_id 
            WHERE filegroups.is_read_only = 1 
           AND indexes2.[object_id] = indexes.[object_id] 
           AND indexes2.[index_id] = indexes.index_id 
           AND destination_data_spaces.destination_id = partitions.partition_number
            ) THEN 1 
         WHEN EXISTS 
            (
            SELECT * 
            FROM sys.indexes indexes2 
            INNER JOIN sys.filegroups filegroups 
            ON indexes.data_space_id = filegroups.data_space_id 
            WHERE filegroups.is_read_only = 1 
           AND indexes.[object_id] = indexes2.[object_id] 
           AND indexes.[index_id] = indexes2.index_id
            ) THEN 1 
         WHEN indexes.[type] = 1 
        AND EXISTS 
            (
            SELECT * 
            FROM sys.tables tables 
            INNER JOIN sys.filegroups filegroups 
            ON tables.lob_data_space_id = filegroups.data_space_id 
            WHERE filegroups.is_read_only = 1 
           AND tables.[object_id] = objects.[object_id]
            ) THEN 1 
         ELSE 0 
      END AS OnReadOnlyFileGroup, 
      0 AS ResumableIndexOperation, 
      stats.stats_id AS StatisticsID, 
      stats.name AS StatisticsName, 
      stats.no_recompute AS NoRecompute, 
      stats.is_incremental AS IsIncremental, 
      partitions.partition_id AS PartitionID, 
      partitions.partition_number AS PartitionNumber, 
      IndexPartitions.partition_count AS PartitionCount, 
      0 AS [Order], 
      0 AS Selected, 
      0 AS Completed 
   FROM sys.indexes indexes 
   INNER JOIN sys.objects objects 
   ON indexes.[object_id] = objects.[object_id] 
   INNER JOIN sys.schemas schemas 
   ON objects.[schema_id] = schemas.[schema_id] 
   LEFT OUTER JOIN sys.tables tables 
   ON objects.[object_id] = tables.[object_id] 
   LEFT OUTER JOIN sys.stats stats 
   ON indexes.[object_id] = stats.[object_id] 
  AND indexes.[index_id] = stats.[stats_id] 
   LEFT OUTER JOIN sys.partitions partitions 
   ON indexes.[object_id] = partitions.[object_id] 
  AND indexes.index_id = partitions.index_id 
   LEFT OUTER JOIN 
      (
      SELECT partitions.[object_id], 
         partitions.index_id, 
         COUNT(DISTINCT partitions.partition_number) AS partition_count 
      FROM sys.partitions partitions 
      GROUP BY partitions.[object_id], 
         partitions.index_id
      ) 
      IndexPartitions 
   ON partitions.[object_id] = IndexPartitions.[object_id] 
  AND partitions.[index_id] = IndexPartitions.[index_id] 
   WHERE objects.[type] IN('U','V') 
  AND objects.is_ms_shipped = 0 
  AND indexes.[type] IN(1,2,3,4,5,6,7) 
  AND indexes.is_disabled = 0 
  AND indexes.is_hypothetical = 0
   ) 
   IndexesStatistics```