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.
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?