A few opsmgr questions of interest - Question 7

  • Article

1. How does reindexing work in opsmgr? Is it a SQL job? Which one? Is it a rule in the SQL management pack? Which one? Document how all the built in maintenance works in the operational and reporting database and why we don’t need and shouldn’t setup our own standard maintenance jobs.

The mechanism for reindexing and maintenance has changed from the MOM 2005 methods of using jobs.

Operational Database
Reindexing of the operation database is done by using a rule targeted at the root management server. This name of the rule is ‘Optimize Indexes’ and can be easily found by searching the rules. This rule runs daily at 2:30 AM. The management pack containing this rule is sealed so there is no way to modify the schedule for the rule – any changes to the time will not be saved. There is also no override to change the time – this rule only has a disable/enable override. As shown below, the rule calls the p_optimizeindexes stored procedure to perform the reindexing operation on the operational database.

The p_OptimizeIndexes store procedure calls the DomainTableIndexOptimize stored procedure as part of it’ execution

Data warehouse

For the data warehouse, we do our indexing (along with other maintenance) each time the standard grooming and maintenance stored procedure runs. There is no set schedule for these maintenance jobs to be executed but, rather, the frequency at which the stored procedures are called depends on the frequency of data insertion to the data warehouse. Every time data is inserted into the warehouse the dataset ID will be passed to the master maintenance/grooming stored procedure – StandardDatasetMaintenance. This stored procedure launches several other stored procedures to do its job, including the Standard DatasetOptimize stored procedure which is responsible for handling the index manipulation. Another aspect of this maintenance is grooming – for more information on how grooming works on the operational database and the data warehouse, take a look at my blog entry at https://blogs.msdn.com/steverac/archive/2007/12/13/scom-2007-operational-and-datawarehouse-grooming.aspx.