Index Reorganize maintenance job more than 20 times slower

by Krishn   Last Updated April 16, 2018 12:06 PM

I have a reorganize maintenance job which runs daily (I know, this have been altered to weekly) which for the majority of executions it takes around 2 hours however over the weekend has failed with execution times of 22 and 35 hours.

The server instance is quite small with the largest database size being 65 gb.

I have checked auto growth and there haven't been any auto growth processes, also I'm currently running the script below to show index fragmentation however this has been running for over 10 minutes and pulled through nothing.

Please can someone provide possible reasons for the elongated duration of my reorganize job?

    SELECT  TOP 10 
    OBJECT_NAME(ips.OBJECT_ID)
     ,i.NAME
     ,ips.index_id
     ,index_type_desc
     ,avg_fragmentation_in_percent
     ,avg_page_space_used_in_percent
     ,page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
    INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
     AND (ips.index_id = i.index_id)
    ORDER BY avg_fragmentation_in_percent DESC


Related Questions



Rebuild Index not freeing up space

Updated April 06, 2018 11:06 AM


Reorganize Index Task

Updated June 26, 2015 17:02 PM