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
After running for 10 minutes + and filtering to only show user tables, I managed to return results for fragmentation.
The top 237 were >= 80% fragmentation and top 1091 were >= 50% fragmentation.