Ola Hallengren index maintenance - long periods of time between commands?

Question

I run the Ola Hallengren scripts on all of my servers for index and statistics maintenance. When I look through the command log table, I notice long periods of time between a command ending, and the next command starting. Sometimes this gap is over an hour.

Does anyone else observe this on their systems? Is there something I can do to shorten the (I'm guessing) discovery time between items to maintain? Below is the parameter set I'm running them with.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES', 
@LogToTable = 'Y',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y' " -b

So when I run this:

SELECT DATEDIFF(MINUTE, cl.StartTime, cl.EndTime)
, *
FROM master.dbo.CommandLog AS cl
WHERE cl.StartTime > '2014-12-13'
ORDER BY cl.ID

I see this:

Answer 1

The root cause is the DMF sys.dm_db_index_physical_stats in conjunction with the Scanning Modes and heaps.

(My emphasis on the LIMITED portions of the original description)

The mode in which the function is executed determines the level of scanning performed to obtain the statistical data that is used by the function. mode is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the page chains for the allocation units that make up the specified partitions of the table or index. sys.dm_db_index_physical_stats requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in.

The LIMITED mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.

With LIMITED mode, compressed_page_count is NULL because the Database Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for compressed_page_count. The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Results in SAMPLED mode should be regarded as approximate. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.

The DETAILED mode scans all pages and returns all statistics.

The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index.

Reference: sys.dm_db_index_physical_stats (Transact-SQL) | Scanning Modes (Microsoft Docs)

Even if Ola's script is executing the sys.dm_db_index_physical_stats in LIMITED mode, depending on the amount of data, it can take a long time to scan a very large heap. And because you are using @UpdateStatistics = 'ALL' you are telling the script to update all the statistics (INDEX and COLUMN) which will include statistics on heap columns.

Possible Solution

You might want to consider not updating the statistics on all objects, but instead limiting the scope to INDEX or then consider changing the following parameter:

@OnlyModifiedStatistics = 'Y'

the default is N

Update statistics only if any rows have been modified since the most recent statistics update.

Reference: SQL Server Index and Statistics Maintenance (ola.hallengren.com)

Answer 2

Digging a bit on this, using a common query to retrieve index fragmentation took just about an hour even to return 160 rows on a 1.5TB database:

SELECT  s.name AS schemaname ,
        t.name AS tablename ,
        t.object_id ,
        i.name AS indexname ,
        i.index_id ,
        x.page_count ,
        x.avg_fragmentation_in_percent ,
        x.avg_page_space_used_in_percent ,
        i.type_desc
FROM    sys.dm_db_index_physical_stats(DB_ID(
                                ), NULL, NULL, NULL, 'LIMITED') x
        INNER JOIN sys.tables t ON x.object_id = t.object_id
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        INNER JOIN sys.indexes i ON x.object_id = i.object_id
                                AND x.index_id = i.index_id
WHERE   x.index_id > 0
        AND alloc_unit_type_desc = 'IN_ROW_DATA'
        AND x.page_count > 1000;

Which leads me to believe that when this part of the index maintenance routine runs, it's taking a very long time at the beginning of a new database to collect initial information and build commands.

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

EXECUTE sp_executesql @statement = @CurrentCommand12, @params = N'@ParamDatabaseID int, 
@ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, 
@ParamFragmentationLevel float OUTPUT, @ParamPageCount bigint OUTPUT', 
@ParamDatabaseID= @CurrentDatabaseID, @ParamObjectID = @CurrentObjectID, @ParamIndexID = 
@CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, 
@ParamFragmentationLevel = @CurrentFragmentationLevel OUTPUT, @ParamPageCount = 
@CurrentPageCount OUTPUT

Answer 3

I have seen this behaviour - even when rebuilding online - you still need a schema modification (SCH-M) lock to change the index over.

On a busy table, this can take time to acquire.