HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
periodscommandslongtimehallengrenmaintenancebetweenindexola

Problem

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:

Solution

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

Code Snippets

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;
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

Context

StackExchange Database Administrators Q#86111, answer score: 5

Revisions (0)

No revisions yet.