First published on TECHNET on Feb 27, 2014
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]
++ OBJECT_NAME(stat.object_id), ind.name, ( ,stat.avg_fragmentation_in_percent)
sys.dm_db_index_physical_stats(DB_ID(), , , , ) stat
stat.avg_fragmentation_in_percent > 10.0 ind.type
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 !
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.