MOM Datawarehouse reindexing script

MOMResources.org are hosting an unsupported reindexing script for the SystemCenterReporting database. (Full details here https://www.momresources.org/scripts-momadministration.shtml).

The script is recommended for keeping the SystemCenterReporting database performant. When scheduled, the stored procedure p_maint_ExecuteMaintTasks will reindex, and update statistics on all those tables in the database matching the criteria (LogicalFramentation from a DBCC SHOWCONTIG. see the readme file in the scdw_reindex.zip file on the above link).

Also of note is that the reindex is performed over time (7 days) due to the size some of these indices become (it can be extremely time consuming for indexes on a table such as SC_SampledNumericFact_Table). Additionally the indices to rebuild are computed during the scheduling stored procedure.  Hence adding a management pack such as the Availability MP, or the Summary Reporting MP, which adds tables/indexes to the schema, will have those indexes rebuilt too. Pretty cool!

The scheduling status can be determined by querying the table dbo.DBMaint_Task. The Interval column can be used to determine on which day cycle (day cycle 1-7) which indexes will be reindexed using the command in the Command column. The ItemWork column is computed from the number of dpage (pages allocated) in the index. The CompletedDateTime is populated with the date and time that command actually executed.

e.g.

SELECT * FROM dbo.dbMaint_Task ORDER BY CompletedDateTime DESC, Interval

will give an indication of which tasks completed on which date and time and what day interval relates to what CompletedDateTime.