patternsqlMinor
Ola Hallengren index maintenance - long periods of time between commands?
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.
So when I run this:
I see this:
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' " -bSo 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.IDI 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:
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.
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 OUTPUTCode 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 OUTPUTContext
StackExchange Database Administrators Q#86111, answer score: 5
Revisions (0)
No revisions yet.