ConfigMgr 2012 Rebuilding Indexes criteria
Published Feb 14 2019 12:58 PM 2,760 Views

First published on TECHNET on Feb 27, 2014


Hi Folks,

Todays’ post is about the ‘Rebuilding the Indexes’ criteria used by the ConfigMgr Maintenance task when it runs as per schedule. There were questions regarding which indexes are actually rebuild touched and what are not.

The option for enabling the Rebuild Indexes is shown below –


- The rebuilding of the indexes happens for the objects only if their FRAGMENTATION PERCENT is more than 10%.

- The index of type ‘HEAP’ are not considered (type = 0) for rebuild. Generally the types that are rebuild are CLUSTERED, NONCLUSTERED and XML.


The below query can be ran to find the tables whose indexes would be rebuild if the Maintenance task runs. [Sorted by the Most Fragmented first]


 select distinct 
+ '.' + OBJECT_NAME(stat.object_id),, convert(int,stat.avg_fragmentation_in_percent) 
as Fragmentation_percent
 from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') stat
sys.indexes ind on 
stat.object_id=ind.object_id and stat.index_id=ind.index_id
sys.objects obj on obj.object_id=stat.object_id
sys.schemas sch on obj.schema_id=sch.schema_id
 where is not 
null and stat.avg_fragmentation_in_percent > 10.0 and ind.type 
> 0
by convert(int,stat.avg_fragmentation_in_percent) 


Sample Output:



For indexes >30% fragmented we use


alter index <indexname> on <tablename> REBUILD WITH (ONLINE=ON)

If that fails

alter index <indexname>  on <tablename>  REBUILD WITH (ONLINE=OFF)


For < 30% fragmented we just reorganize


alter index <indexname>   on <tablename>   REORGANIZE


How to track this in logs? 


We log reindexing in the SMSDBMon.log in the %ConfigMgr%\Logs


SMS_DATABASE_NOTIFICATION_MONITOR    5804 (0x16ac)    Task Rebuild Indexes is due now. 
SMS_DATABASE_NOTIFICATION_MONITOR    5804 (0x16ac)    Executing Rebuild Indexes 


SMS_DATABASE_NOTIFICATION_MONITOR    5804 (0x16ac)    Reindexing completed

Hope it Helps !


Umair Khan
Support Escalation Engineer | Microsoft System Center Configuration Manager 





Version history
Last update:
‎Apr 01 2019 07:59 AM
Updated by: