Please add "set statistics profile on" in the index optimize stored proc

[ravirajv0120]

In the IndexOptimize stored proc please add "set statistics profile on" for every dynamic sql generated for index rebuild command. This will facilitate capturing the progress of the rebuild in the dm_exec_query_profiles and helpful for estimating the completion time for rebuild of large indexes. In my case I have a clustered column store index on a 8 Billion row table and take a very long time to rebuild and without knowing the progress it is hard to tell the customer when it will complete.

[samot1]

why do you not look into the master.dbo.commandlog table to find out, how long each statement for each index took?

For my purposes (weekly maintainance) I created the following view (feel free to use / modify it for your needs):

SELECT calc.year
     , calc.iso_week
     , calc.duration_seconds                                        AS duration_seconds
     , CAST(calc.duration_seconds / 3600.0 AS DECIMAL(9, 2))        AS duration_minutes
     , CAST(calc.duration_seconds /   60.0 AS DECIMAL(9, 2))        AS duration_hours
     , IIF(cl.Command LIKE '%REBUILD%', 'REBUILD', 'REORGANIZE')    AS operation
     , cl.ExtendedInfo.query('ExtendedInfo/Fragmentation').value('.', 'DECIMAL(5, 2)')                                  AS fragmentation
     , cl.DatabaseName
     , cl.SchemaName
     , cl.ObjectName
     , cl.IndexName
     , cl.PartitionNumber
     , CAST(cl.ExtendedInfo.query('ExtendedInfo/PageCount').value('.', 'bigint') * 8.0 / 1024        AS DECIMAL(12, 3)) AS size_mb
     , CAST(cl.ExtendedInfo.query('ExtendedInfo/PageCount').value('.', 'bigint') * 8.0 / 1024 / 1024 AS DECIMAL(12, 3)) AS size_gb
     , calc.index_type_desc
     , cl.Command
     , cl.StartTime
     , cl.EndTime
     , cl.ErrorNumber
     , cl.ErrorMessage
     , cl.ObjectType
     , cl.ID
     , ROW_NUMBER() OVER (ORDER BY calc.year, calc.iso_week, calc.duration_seconds DESC) AS sort_order -- sorts the result implicit
  FROM master.dbo.CommandLog                                          AS cl
 CROSS APPLY
      (SELECT DATEDIFF(SECOND, cl.StartTime, cl.EndTime) AS duration_seconds
            , CASE cl.IndexType -- see https://docs.microsoft.com/de-de/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql
                   WHEN 0 THEN 'Heap'
                   WHEN 1 THEN 'Clustered'
                   WHEN 2 THEN 'Nonclustered'
                   WHEN 3 THEN 'XML'
                   WHEN 4 THEN 'Spatial'
                   WHEN 5 THEN 'Clustered columnstore INDEX'
                   WHEN 6 THEN 'Nonclustered columnstore index'
                   WHEN 7 THEN 'Nonclustered hash INDEX'
              END                                         AS index_type_desc
            , YEAR(cl.StartTime)                          AS year
            , DATEPART(iso_week, cl.StartTime)            AS iso_week -- not date, since the IndexOptimize runs over midnight
      ) AS calc
 WHERE cl.CommandType = 'ALTER_INDEX'

GO

PS: something is wrong with the code-tag here in