0

I have implemented Ola Hallengren's maintenance solution for index maintenance only. When I run it there are large gaps of time between index rebuilds/reorgs.

It runs for several minutes before the first index reorg is logged. Then, it logs a few and hangs for several minutes again. The job has been running for over 30 minutes and only 14 indexes are processed, and yet the longest running reorg is 2 seconds.

In the image below I put a box around the ones that appear to occur together, based on the StartTime.

enter image description here

What is it hanging on?

When I check currently running queries, I see this:

/*
This query text was retrieved from showplan XML, and may be truncated.
*/
(
        @ParamDatabaseName NVARCHAR(max)
        ,@ParamObjectID INT
        ,@ParamIndexID INT
        ,@ParamPartitionNumber INT
        ,@ParamFragmentationLevel FLOAT OUTPUT
            ,@ParamPageCount BIGINT
        OUTPUT
        )

SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent)
    ,@ParamPageCount = SUM(page_count)
FROM sys.dm_db_index_physical_stats(DB_ID(@ParamDatabaseName), @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, 'LIMITED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
    AND index_level = 0

For the record, this is the script that runs the job:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 30,
@MinNumberOfPages = 25,
@UpdateStatistics = NULL,
@LogToTable = 'Y'

So does it rerun sys.dm_db_index_physical_stats after every rebuild/reorg?

Browse other questions tagged or ask your own question.