Alter index progress

Question

what's the best way to estimate the rebuild index progress.?

the session of the Alter index , where the Percent_complete from sys.dm_exec_requests always 0 .

Answer 1

sys.dm_exec_requests reports the progress for an Index Reorg, not for Index Rebuild.

But you can see by partition stats size+row count, how many rows are already processed:

SELECT DISTINCT SCH.name AS SchemaName  
      ,OBJ.name AS ObjName  
      ,OBJ.type_desc AS ObjType  
      ,INDX.name AS IndexName  
      ,INDX.type_desc AS IndexType  
      ,PART.partition_number AS PartitionNumber  
      ,PART.rows AS PartitionRows  
      ,STAT.row_count AS StatRowCount  
      ,STAT.used_page_count * 8 AS UsedSizeKB  
      ,STAT.reserved_page_count * 8 AS ReservedSizeKB  
      ,PART.data_compression_desc  
      ,DS.name AS FilegroupName  
      ,(STAT.reserved_page_count - STAT.used_page_count) * 8 AS Unused        
FROM sys.partitions AS PART  
     INNER JOIN sys.dm_db_partition_stats AS STAT  
         ON PART.partition_id = STAT.partition_id  
            AND PART.partition_number = STAT.partition_number  
     INNER JOIN sys.objects AS OBJ  
         ON STAT.object_id = OBJ.object_id  
     INNER JOIN sys.schemas AS SCH  
         ON OBJ.schema_id = SCH.schema_id  
     INNER JOIN sys.indexes AS INDX  
         ON STAT.object_id = INDX.object_id  
            AND STAT.index_id = INDX.index_id  
     INNER JOIN sys.data_spaces AS DS  
         ON INDX.data_space_id = DS.data_space_id  
ORDER BY OBJ.name  
        ,INDX.name  
        ,PART.partition_number  

Answer 2

Hi sakuraime,

Agree with Olaf.
In addition, since SQL Server 2014, we can use sys.dm_exec_query_profiles to monitor rebuild index progress. It can monitor real time query progress while the query is in execution.
For example:

--Run this in a different session than the session in which your query is running.   
--Note that you may need to change session id 54 below with the session id you want to monitor.  
SELECT node_id,physical_operator_name, SUM(row_count) row_count,   
  SUM(estimate_row_count) AS estimate_row_count,   
  CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)    
FROM sys.dm_exec_query_profiles     
WHERE session_id=54  
GROUP BY node_id,physical_operator_name    
ORDER BY node_id;  

Please refer to sys.dm_exec_query_profiles and this thread which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi sakuraime,

The row_count column means the number of rows that were processed.
The estimated_row_count are the estimated rows to be processed. The CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count) means percentage of rows processed.

Best Regards,
Amelia