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: 20.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 an agent 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 WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_DEVICE REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ATTR REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_DEVICE_ATTRIBUTE REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_DEFINITION REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC_DEFINITION REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_NIMBUS_ROBOT REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_DEVICE REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ORIGIN REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_ATTRIBUTE REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CI REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CS REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_RELATIONSHIP_CS_CI REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON CM_DISCOVERY_NETWORK REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON S_QOS_DATA REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2PolicyTargetStatus REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2Device REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2ProfileCheckSum REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2Profile REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2ConfigValue REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2DeviceGroup REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2ProfileCheckSum REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2AuditTrail REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2Template REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2Container REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON SSRV2DevicePackage REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON S_QOS_DATA REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON NAS_TRANSACTION_LOG REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON NAS_ALARMS REBUILD WITH (ONLINE = ON);

The index rebuild should not take more than 15 to 20 minutes...max 30 minutes.