0

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

Additional information

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.

  • 2
    If the job was blocked, or if you added a significant amount of LOB data that the reorganization had to compact, that could cause it to run long. It would be very difficult to give you an answer about something that happened on a server in the past without more information from you. This might be a good time to rethink your index maintenance. – Erik Darling Apr 16 '18 at 12:07
  • Hi @sp_BlitzErik, I have added a bit of information based on the output of my fragmentation script. Is there any other information which would be useful? Also I have changed the schedule to run on a weekly basis rather than daily – Krishn Apr 16 '18 at 12:33
  • No, that doesn't tell me at all if the job was blocked or if you added a lot of LOB data. If you insist on defragmenting, please look at Ola Hallengren's free scripts. – Erik Darling Apr 16 '18 at 12:36
  • 1
    No, sorry. You'd have to have collected the information while it was occurring. – Erik Darling Apr 16 '18 at 12:47
  • 2
    The query would not give accurate result as it does not have where page_count > 1500 to filter out small indexes. There is no point in touching small indexes at all. The reason why it ran so long was because it was blocked and you are not using smart query for index maintenance. – Shanky Apr 16 '18 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.