Umair Khan's blog

Sharing my experience with System Center Configuration Manager and Microsoft.

ConfigMgr 2012 Rebuilding Indexes criteria

ConfigMgr 2012 Rebuilding Indexes criteria

  • Comments 1
  • Likes

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 –

 Criteria:

- 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 sch.name + '.' + OBJECT_NAME(stat.object_id), ind.name, convert(int,stat.avg_fragmentation_in_percent) as Fragmentation_percent

      from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') stat

       join sys.indexes ind on stat.object_id=ind.object_id and stat.index_id=ind.index_id

       join sys.objects  obj on obj.object_id=stat.object_id

       join sys.schemas sch on obj.schema_id=sch.schema_id

       where ind.name is not null and stat.avg_fragmentation_in_percent > 10.0 and ind.type > 0

       order by convert(int,stat.avg_fragmentation_in_percent) desc

===================

Sample Output:

Hope it Helps !

Umair Khan

Support Escalation Engineer Microsoft System Center Configuration Manager  

 

Comments
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment