3 Added the generic tag of the DBMS of relevance for visibility. Removed "edit" (see https://meta.stackexchange.com/a/127655 ).
source | link

Index Reorganize index maintenance job is more than 20 times slower

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

edit: AfterAdditional 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.

Index Reorganize maintenance job more than 20 times slower

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

edit: 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.

Reorganize index maintenance job is more than 20 times slower

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 added 206 characters in body
source | link

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

edit: 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.

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

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

edit: 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.

1
source | link

Index Reorganize maintenance job more than 20 times slower

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