SQL Server Maintenance – Ola Hallengren Index Maintenance Long Periods Between Commands

maintenanceola-hallengrensql server

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:

Sample output

Best Answer

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)

Related Question