DX UIM - data_engine indexing option

Products

DX Unified Infrastructure Management (Nimsoft / UIM) CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

What does the data_engine index maintenance option do and should I enable it?

Environment

Release: 9.x or higher
Component: UIM - DATA_ENGINE

Resolution

The data_engine indexing option was added to the data_engine a long time ago, but in large environments that utilize DB partitioning, it is not recommended nor necessary to enable it.

The index maintenance option, when enabled and run by the data_engine, only runs on the RN, HN, BN, DN, VN tables. In large UIM environments when you have partitioning enabled, as per testing, there is little to no significant gain in performance or advantage in defragmenting the RN, HN, BN, etc., table indexes, especially for large tables.

Note that defrag on large table indexes may take an extremely long time and/or not finish successfully. Defrag on a large table can take more than 18-24 hours and uses up significant DB Server resources to run. These jobs may also hang if the job does not have enough resources to be able to run to successful completion.

As confirmed during testing, large tables with high index fragmentation may be successfully defragged as a single job/separately, down to 30-70% fragmentation, but in a few days the high fragmentation will often return, e.g., back up to ~99.98%.

The good news is that, as long as you are using database partitioning, there is no need to defrag the RN, HN, BN, VN, etc. tables, but, some specific tables listed below must be defragged each day (during non-business hours if possible), by running a daily defrag job on them. There are a few options available on how to run a daily job to defrag the table indexes. Consult with your DBA to decide on what method to use. Please make sure your data_engine’s data_management_timeout is set to 65536.

IMPORTANT:

The data_engine table partitioning option is ONLY officially tested and supported in the Enterprise version, e.g., Microsoft SQL Server Enterprise Edition (NOT standard or other versions), even though the latest versions of the SQL Server Standard edition include the partitioning feature.

Ask your DBA to set up a job to run this DAILY table index defrag job (during non-business hours or during periods of low message traffic if possible).

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM REBUILD;

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ATTR REBUILD;

ALTER INDEX ALL ON CM_DEVICE REBUILD;

ALTER INDEX ALL ON CM_DEVICE_ATTRIBUTE REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_DEFINITION REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC_DEFINITION REBUILD;

ALTER INDEX ALL ON CM_NIMBUS_ROBOT REBUILD;

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ORIGIN REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_ATTRIBUTE REBUILD;

ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CI REBUILD;

ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CS REBUILD;

ALTER INDEX ALL ON CM_RELATIONSHIP_CS_CI REBUILD;

ALTER INDEX ALL ON CM_DISCOVERY_NETWORK REBUILD;

ALTER INDEX ALL ON S_QOS_DATA REBUILD;

ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD;

ALTER INDEX ALL ON NAS_TRANSACTION_LOG REBUILD;

ALTER INDEX ALL ON NAS_ALARMS REBUILD;